Error in default value of a numeric text box

[Guest]

Dear friends,

I use this expression for the "default value" property of a numeric text box:
= nz(dmax("orderno","orders","client='" & me.client & "'"),1)

and I always get the #Error in that field.

I have to mention that the field I am talking about is inside a subform. If
I try the same in the parent form everything is OK.

Can someone help pls?

Thanks and regards
Catalin

 

[Stefan Hoffmann]

hi Catalin,

I use this expression for the "default value" property of a numeric text box:
= nz(dmax("orderno","orders","client='" & me.client & "'"),1)

Shouldn't that be =Nz(DMax(), 0) + 1?

and I always get the #Error in that field.

Is Me.Client a field or a control? Try using

Replace(Nz(Me![Client], ""), "'", "''"), if it is a field,

otherwise use

Replace(Nz(Client.Value, ""), "'", "''").

The Replace() is escaping the '.


mfG
--> stefan <--

 

[Guest]

Hi Stefan,

Thanks for your answer.

I made a mistake.
The sintax is: = nz(dmax("orderno","orders","client='" & me.client &
"'"),1)+1.
Me.client referes to a control bound to the field client part of orders table.

I do not know how to use your suggestion with replace function. Can u b more
specific?

I tried it but I still get the #Error.

Thanks and regards,
Catalin

hi Catalin,

I use this expression for the "default value" property of a numeric text box:
= nz(dmax("orderno","orders","client='" & me.client & "'"),1)

Shouldn't that be =Nz(DMax(), 0) + 1?

and I always get the #Error in that field.

Is Me.Client a field or a control? Try using

Replace(Nz(Me![Client], ""), "'", "''"), if it is a field,

otherwise use

Replace(Nz(Client.Value, ""), "'", "''").

The Replace() is escaping the '.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Catalin,

I made a mistake.
The sintax is: = nz(dmax("orderno","orders","client='" & me.client &
"'"),1)+1.

This will give you 2 for the first entry.

Me.client referes to a control bound to the field client part of orders table.
I do not know how to use your suggestion with replace function. Can u b more
specific?

As you are using single quotes in the DMax criteria, I have to assume
that Client is a string. Strings can contain single quotes, in such a
case you'll get an error, due to a wrong criteria string. E.g.

Client = "d'Artagnan"

"client='" & me.client & "'"
=> "client='d'Artagnan'"

This is an invalid criteria, cause the string ends after 'd' and
Artagnan is interpreted as a keyword which don't exists.

I tried it but I still get the #Error.

Check the spelling of your field and table names. I have no english
version at my hands, but in the german localized version you have to use
; as seperator in the parameter lists.

Try

=Nz(DMax("OrderNo";"Orders";"Client='"&Replace(Client.Value;"'";"''")&"'");0)+1


mfG
--> stefan <--