Leading zero in automatically assigned number

[WalterInOz]

I have a form with a field ProjectNumber_StudyNumber. This field gets it data from a query in which data are combined from 2 fields: "ProjectNumber" and "StudyNumber". Format of the combined field is 1.10/005, 1.10 os the projectNumber, 005 is the StudyNumber.

This all works good but when you create a new record it requires the user to look up the last used studynumber, add +1 and fill in the new studynumber. It works but I'd like to get that automated. I've almost achieved that now but am stuck at the format of the numbering. I'm using this code I found on this forum to generate the new studynumber and that works well:

Me!txtNewStudyNumber.DefaultValue = Nz(DMax("[StudyNumber]", "tblStudyPlans"), 2) + 1

The problem is that if I have a leading zero in the studynumber (and I have for all of the first 99 studies in each project), as in e.g. 015 or 001, these zeroes are not included in the next number and that throws my sorting on studynumber, 2 is seen as a higher number than 011. The 2 of course should be 002. How do I get the leading zeroes?
In the table the field is numerical with field size 3. Input mask is 000;;

What am I doing wrong?

Thanks for your help,

Walter

 

[boblarson]

You can set the sort by creating a separate field in your form's underlying query to set the format then the sort ascending.

For example create a new column in the query by putting this in a blank column in the QBE grid:
NumSort:Format(YourNumberFieldHere,"000")

And then set the sort order to Ascending.

 

[WalterInOz]

Thank you Bob!

This is exactly what I needed. Great tip.