Formating number

[schalkse]

Have searched for a while, but could not find an answer. Probably is very simple.

I have a DB with orders, in order to create an ordernumber i count the orders made this year, get a result and want that result to be 3 digits (eg counted 3 should be "003")
Here is my code, but wont get what i want.

strDatumNu = Format(Now(), "yy")
lngVolgnummer = DCount("[Jaartal]", "tblLevOrder", "[Jaartal] = '" & strDatumNu & "'")
lngVolgnummer = FormatNumber(lngVolgnummer, "000")

If i put "&&&" or "___" instead of "000" i get an type mismatch error

If i have 3 records, it always returns me 3 instead of 003.

Any help?
Thanks, Tom

 

[gemma-the-husky]

you need to thing about this.

the presentation of the data is disctinct from the storage of the data. You can store it as a number, and still present it stored in the way you require, so the numerical reference is storing a sequential identifier

Alternatively you can convert it into a string so that the reference is actually the characters "003" , rather than the number 3 - in fact it is probably this difference that is giving you your type mismatch error

At the back of this is the issue of what happens after you progress to order number 1000. If you are storing numbers, you move ot number 1000. and everything stil lworks (although you may need to change your number masking to present the number as a 4 digit number. If oyu are storing character strings, what happens then?

.