formatiing numbers?

[Maclain]

Afternoon all,

I'm trying to get an invoice number field to auto generate the next number, keeping the format as "00000"

this is what I have, which gets the next number but drops the leading 0

Private Sub Customer_AfterUpdate()
 If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
    Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1)
    DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

invoice numbers are 04024, 04025 etc

any ideas how I keep the formatiing?

 

[pr2-eugin]

Use the Format function maybe?

? Format(0, "00000")
00000
? Format(425, "00000")
00425

 

[Maclain]

I've actually tried that, but can't figure out how to get it to work..

 Private Sub Customer_AfterUpdate()
 If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
    Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1 & format([InvoiceNumber], "00000"))
    DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

Doesn't give me the desired result.

 

[pr2-eugin]

Doesn't give me the desired result.

Because you need to format the Number you wish to use. Not just add the format to the end.

Private Sub Customer_AfterUpdate()
    If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
        Me.[InvoiceNumber] = [COLOR=Red][B]Format([/B][/COLOR]DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1[COLOR=Red][B],  "00000")[/B][/COLOR]
        DoCmd.RunCommand acCmdSaveRecord
    End If
End Sub

What is the Data Type of Invoice Number? The above might Err if it is Number.

 

[Maclain]

Genius. Thanks

 

[pr2-eugin]

No problem ! Good luck.

PS: Thanks for giving my 1,800th Thanks !