Question Display numbers as "0001" or "0012"

[remcopeters]

- I have a table "Quotes" with fields "UserID" and "Quotenumber"
- I have a form "Make quote" with a textfield "txtQuotenumber" and "txtUserID"

With the following code "txtQuotenumber" gets the highest quotenumber for the current "txtUserID" from the "Quotes" table:

=DMax("[Offertenummer]";"[Offertes]";"[Offertes].[Gebruikers-ID]=" & Formulieren![Offerte formulier]!txtGebruikersID)+1

It displays quotenumber 1 as "1" but what I actually want is it to be displayed as "0001". Quote 211 will then be displayed as "0211".

I tried this by entering "0000;;_" into the property "input mask" of "txtQuotenumber" but that results in "1".

How can I manage to do this?

 

[nanscombe]

This should do what you want ..

=Format(DMax("[Offertenummer]";"[Offertes]";"[Offertes].[Gebruikers-ID]=" & Formulieren![Offerte formulier]!txtGebruikersID)+1,"0000")

ETA: Or, more simply ...

 

[Galaxiom]

Enter 0000 in the Format property.

 

[remcopeters]

Thanks Nanscombe, that does the trick!

 

[remcopeters]

Worked in Access 2003, but this doesn't seem to work in Access 2010....?

 

[nanscombe]

... this doesn't seem to work in Access 2010....?

In honour of the Pantomime season ...

Oh yes it does.