Error in default value of a numeric text box

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
hi Catalin,
I use this expression for the "default value" property of a numeric text box:
= nz(dmax("orderno","orders","client='" & me.client & "'"),1)
Click to expand...
Shouldn't that be =Nz(DMax(), 0) + 1?
and I always get the #Error in that field.
Click to expand...
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 <--
 
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

Stefan Hoffmann said:
hi Catalin,
I use this expression for the "default value" property of a numeric text box:
= nz(dmax("orderno","orders","client='" & me.client & "'"),1)
Click to expand...
Shouldn't that be =Nz(DMax(), 0) + 1?
and I always get the #Error in that field.
Click to expand...
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 <--
Click to expand...
 
hi Catalin,
I made a mistake.
The sintax is: = nz(dmax("orderno","orders","client='" & me.client &
"'"),1)+1.
Click to expand...
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?
Click to expand...
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.
Click to expand...
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 <--
 
Back
Top