Need sequential, formatted number

[Dave 14867]

Hello all, a newbie here with a simple issue but I can't remember how to do it.

I need a sequential number to be generated, which I have done with DMax(), but I also need to now take that number and "pad" it with leading "0's" so I end up with a 4 digit number and save it as a string, example, I generate the number 143, but what I really need is 0143 and need to save it as a string, text actually.

How can I do this?

Thanks in advance for you assistance

 

[JHB]

The below code should do it, depending of where you place it and from which country you're, the , should be replaced with ;

Format(YourNumber[B][COLOR=Red],[/COLOR][/B] "0000")

 

[Dave 14867]

JHB,

That doesn't work, even If I replace the , with a semicolon, it give me a compile error. The code I have is shown below, maybe I am trying to insert it in the wrong place. Here is what I have, by the way, I am in the US.

Public Function Create_NCR_Number()
Format(Number, "0000")
End Function

The Name of the field in my application is "New NCR Number" in table Called NCR

I am using Access 2007 on Windows 7

 

[missinglinq]

Not sure why you're attempting this in a Function, as it's something you'd only want to do from a single place, in code, but I think you need to replace the Field name with the DMax Expression you're using, something like this:

Me.[New NCR Number] = Format(DMax("[New NCR Number]", "NCR") + 1, "0000")

It should probably go in the Form_BeforeUpdate event, to be done at the last possible second before saving the Record, and decreasing the chances of getting a duplicate of the number, if this is used in a multi-user environment.

Linq ;0)>

 

[Dave 14867]

MissingLinq,

That worked perfect, that you very much.

I did put it where you suggested, which was where I had originally tried to get something to work but then I thought about putting it in a function. I guess I have some brushing up to do, been away from programming for far too long.

 

[missinglinq]

Functions, of course, have there uses, generally when a task requires a number of lines of code and the task will need to be called from different places in code.

But if a single line of code is needed, and it will only be needed in one place, you're simply replacing one line of code with another line of code...so what's the point?

And, down the line, when you or someone else needs to understand the code, they'd have to go and track down the Function, rather than simply look at the one line, right there, in front of them.

Glad we could help!

Linq ;0)>

 

[Dave 14867]

Missinglinq,

I was incorrect, I thought it was working, and it did for one record, however it doesn't add the "Number" in it's field to the new record. Below is the code, I made some modifications to the table names and field names but this should be the final.

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.Number = Nz(DMax("Number", "NCR Table")) + 1

Me.[Number] = Format(DMax("[Number]", "NCR Table") + 1, "0000") 'Adds 1 to last number and formats correctly to pad with leading 0's
'Need to stuff the result into "Number" column in table
Me.[New NCR Number] = "50-09-" + [Number]

End Sub

Hopefully I don't need to make any other changes, maybe I goofed something up.

If I manually add the next sequential number to the "Number" field, it works great, but each new record has the same "50-09-" string (last 4 digits omitted") because the number never gets added to the new record.