Serial numbering system (1 Viewer)

I have the following code for the text field (Before Update)

If IsNull(Me![DyNo]) Then
Me![DyNo] = Format(Nz(DMax("[DyNo]", "[tblAllDet]", "[TheYear]='" & Year(Date) & "'"), 0) + 1, "00000")
End If
Me![DyNo] = Format([DyNo], "00000")

The code starts giving numbers from 00001, 00002, 00003 and so on. The problem was that when I have to search, I have to type the zeros before the number i.e. 00007, 00008 etc. otherwise the search form doesn’t show the relevant record. Ideally, I would like the numbering system to be 1, 2, 3, etc. (without the zeros). I tried changing the code to “0” but with the single “0”, I am unable to insert records beyond No.10. Is there a way to change this code so that I get only 1, 2, 3 etc and not with preceding zeros ? Grateful for help.

Sure; drop the format function.

But Sir, I need the Format function as I want the number to start from 1 on new year. If I remove, I may not get this feature. Any solution?

The format function is irrelevant to that goal. That's being accomplished by the

nz(Dmax()) + 1

All the format function is doing is adding the zeros.

I changed the code as under:

If IsNull(Me![DyNo]) Then
Me![DyNo] = Nz(DMax("[DyNo]", "[DiaryTable]", "[TheYear]='" & Year(Date) & "'"), 0) + 1
End If
Me![DyNo] = [DyNo]

With this changed code, I am unable to do data entry after No.10. After 10, it repeatedly gives No.10. Please help.

The datatype for DyNo must have been Text. If you have not changed it to Long, then DMax() will return 9 as the max of the text field so you will always get 10 as the result. So change your datatype to Long.

I changed the datatype to Number - Long Integer. Now the DyNo field does not display any number at all..

Initially I had this field with Autonumber. During data entry, if the user cancels, the number is lost. The result is that I have a few missing numbers.

So I changed it to text with DMax as above and it was working fine. But I didnt like the zeros to precede. Any help please?

Here is the issue. There are serial numbers and there are autonumbers.

Autonumbers are automatic but stand-alone. They have various nice properties including guaranteed uniqueness, but "contiguously numbered" is NOT one of their properties. If the number has to have a specific meaning, then you never want to use autonumber.

Your comment about searching tells me you want your number to be both fish and fowl at the same time, and therein lies your problem - contrary goals. I very strongly suggest you re-think your numbering scheme. Then remember this: If it is text, you have to search it like text. If it is a number, you have to search it like a number. And because you are making it a compound number, you are going to forever hate this result as long as you have this idea in your head about how to use it.

If you make the year and serial number two different numeric fields that become a COMPOUND key, you could (if you wished) format them one way for output and a different way for searching. Combined into a single field as they are, you are guaranteeing headaches as you wrestle with a compound number that isn't stored as such. By the way, the same exact concept would apply to almost ANY database, not just Access. This isn't an Access quirk. Sorry to say it this way, but it is a concept-level flaw in your design.

gear said:

I changed the datatype to Number - Long Integer. Now the DyNo field does not display any number at all..

Initially I had this field with Autonumber. During data entry, if the user cancels, the number is lost. The result is that I have a few missing numbers.

So I changed it to text with DMax as above and it was working fine. But I didnt like the zeros to precede. Any help please?

Click to expand...

Because your DyNo is populated with text, the data will disappear when you change it to long because you can't hold text in a number field. What I would do is create a new field and use an update query to populate this using either CLong() or Val() to convert your text to a number. Then delete the DyNo field and rename your new field to DyNo.

An I agree with the Doc Man's wise words.

Thanks folks. It is for these wise guidance I love this forum.

Thank you for advise and help

As I am new to this Access Programming, I committed the mistake of changing the field to text. After The Doc Man's advice, I changed the DyNo field to number and retained the DMax code with single "0". I changed the format for the DyNo field to General number. I redefined the search criteria etc. and now everything is working fine. Thanks once again.