Re: #Name? error from Default Value expression

You left out the colon (:).
"NL:Lesion Number" without the quotes.

Regards,
Andreas

Ted wrote: 
andreas,

i think i've run out of chits :-)

at the point where i typed "NLLesion Number" and attempted to save it as qryGetPatientNumber, it returns an error message "The expression you entered contains invalid syntax" followed by "You may have entered an operand without an operator".

bummer. i've got another approach i'm going to try but thanks a 1,000,000 for all the help and bandwidth.

-ted



"Andreas" wrote:


Last attempt:
- Create a query in design view.
- Add the table [Lesions: Non-Target] as your datasource.
- Bring in the fields [Lesion Number] and [Patient Number].
- Click on the Sigma button on the toolbar to change the query to a Summary Totals query.
- In the Totals row for the field [Lesion Number], select Max.
- In the Totals row for the field [Patient Number], select Where.
- In the Criteria row for the field [Patient Number], enter
[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]
- In the Field row for the field [Lesion Number], in front of the text that is already there, type LN:
- Save the query as "qryGetPatientNumber" (without the quotes).

In the form where you want this as a default value, select the control and set the ControlSource property to:
=Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Provided the default for the Patient number works (in the other control) this should work, unless there is a problem with your names or data types. If this does not work, put a blank, new text box on the form and enter the above and see what value is returned. Just to make sure, I take it the Lesion number is a number field?

Regards,
Andreas


Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
>>& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
>>Number]),0) + 1
>>


Ted wrote:

i did snooping in the Access KB website and came up w/ KB 209785 which speaks to a prohibition against using circular references to a control name in both the Name and Control Source properties.

a quote

"If you create a form based on a table or query and drag a numeric field onto the form, by default the ControlSource property and the Name property are given the name of the actual field in the underlying table or query. If you change the ControlSource property to an expression that includes the Name property, you see "#Error" in this field when you switch to Form or Data*** view.

The following example demonstrates an expression within the ControlSource property: Name: Person_ID
ControlSource: =[Person_ID] + 1 "



"Andreas" wrote:



Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]),0) + 1

Regards,
Andreas


Ted wrote:


hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar?  hmmmm....

what could it be this time? did it matter that the controls Patient and Lesion number are comboboxes and not textboxes?


"Andreas" wrote:




In your DMax function, you used:
  "Lesions: Non-Target"
I used:
  "[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]", "[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas


Ted wrote:



what's different about your rendition of my IIF statement and the one i wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


"Andreas" wrote:





Use square brackets for your datasource (necessary because of your naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:




amongst other controls, my table underlying this form has a PK comprising "Patient Number" and "Lesion Number" as they are also called on the continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions: Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets completed we'd expect there to be nothing in the "Lesion Number" field so the sequence would start with the integer value '1'. the next time and every other when a new record got added via the form we'd expect the Lesion Number to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?






.

Loading