200810310001 1

[matrixindicator]

I like to create a composed unique ordernumber.

First part is the date 20081031 (it should succeed with format())
Second part should be an incremental number with leading zeros starting everyday with 1...

For the second part it would be easy to use an autonumber, but he schould start every new day from the number 1.
Question is, any IDea how to create an automatic solid (100% working fine) second part with leading zeros.

0001
0002
0003

regards,

 

[MichaelRed]

see the faqs particularly re autonumber. been there done that ... sort of



MichaelRed

 

[fneily]

Your ordernumber will be a calculated piece of data, so you wouldn't store it in a single field. So you'd have one field storing the date then the other as your incremental. Then in the forms, reports you'd concatenate them. Having two separate fields will also let you group by date and use the DMAX to produce the next increment.

 

[khicon73]

Use this one, hope it help...

Private Sub txtDate_BeforeUpdate(Cancel As Integer)
Dim maxIdx, Criteria As String
Dim namIdx As String, namDate As String, namTable As String

If Me.NewRecord Then
namDate = "DateField"
namIdx = "IDField"
namTable = "tblTable"
Criteria = "Day([" & namDate & "]) = " & Day(Me(namDate)) & _
" And " & _
"Month([" & namDate & "]) = " & Month(Me(namDate)) & _
" And " & _
"Year([" & namDate & "]) = " & Year(Me(namDate))

maxIdx = DMax("[" & namIdx & "]", namTable, Criteria)

If IsNull(maxIdx) Then
Me(namIdx) = "0001" 'Start over on every day
Else
Me(namIdx) = Format(CStr(maxIdx + 1), "0000")
End If
End If
End Sub

 

[khicon73]

Add date such as 200810310001, then use this:


If IsNull(maxIdx) Then
Me(namIdx) = Format(Me.txtDate, "yyyymmdd") & "0001" 'Start over on every day
Else
Me(namIdx) = Format(Me.txtDate, "yyyymmdd") & Format(CStr(maxIdx + 1), "0000")
End If

 

[matrixindicator]

I need to test it. DMax function seems great to get the highest number.
I don't see it for the moment : If I use an autoIncrement number (autonumber), the lasest available won't be 1. My idea was to create avery new day automatically a temp help table with an autonumber, but perhaps this is a worse solution.
I will read it again to see how your proposals works.

 

[matrixindicator]

I have read it again, I think I understand it.
You look to the latest availaible ID for a field where the date is equal to the date of today. If he can't find one he takes as ID "0001", for the next record he found with DMax function 0001, and for the next 0002 ...

tx khicon73, great solution !