unique number-derived from date

[dazedandconfuzed2]

Okay, I need some help here.

I am creating order forms. Every order gets a unique number
(a purchase order #) that needs to be automatically generated when a new record is created. Here's the scheme
for concocting the number.

It is a 9 digit number. eg: 305091901

the first digit is the last digit of the year (3)
the next two are the month (may-05)
the next two are the day (09)
these need to be generated by the calendar

the next two #s are always 19

followed by sequential #s starting at one, fresh
every day at 01 (starting over again, prefaced by the date and 19), going as high as 99

help me, please!

 

[danvlas]

You should create 2 fields. The first (text data type) can hold a value calculated as:

Val(format(date,"yy&quot) & Format(Month(Date), "00&quot & Format(Day(Date), "00&quot

The other (incremental number) can be calculated as:
Nz(DMax("FirstField","TableName", "FirstField = '" & Me!FirstField & "'&quot,0) + 1

Then in any query, form or report, just concatenate them:

FirstField & "19" & Format(SecondField, "00&quot

This will work for a single-user application. For multi-user, everything changes...

One tiny question...What happens with the order number 100...will it wait till next day???




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dazedandconfuzed2]

Thanks! This sounds promising, but I really need it in one
field so that it becomes a unified number. Perhaps I dont
completely understand this, but it seems as if I will end
up with two fields side by side.

At 100 a the number would become 10 digits long which
would put it out of numerical sequence, moving to the
left a whole place...thus messing up the true sequence of
the orders, throwing of searches and print runs

 

[danvlas]

You think you need it in one field. The truth is you need to see it in one field...which is what a query does...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[jrbarnett]

Hi,

You will need a table to record the purchase numbers that have been issued on a per date basis.
I have assumed here you only need to track the latest order number, the order details are saved elsewhere.

It needs a structure of OrderDate type date/time set as Primary key and LastNumber type Byte. Save the table as IssuedOrders. Using a byte field will allow up to 255 orders per day.

public function GetNumber As String

Dim strOrderNumber As String
Dim strDate As String
Dim bytTemp as Byte

strDate = Format (Date(), "dd/mm/yyyy")

strOrderNumber = Right (strDate,1)  ' the 3
strOrderNumber = strOrderNumber & Mid (strDate, 3, 2) ' the 05
strOrderNumber = strOrderNumber & Left(strDate, 2) ' the 09
strOrdernumber = strOrderNumber & "19" ' add the fixed digits

DoCmd.SetWarnings False
If DCount ("*", "IssuedOrders", "OrderDate=#"& Date() & "#") = 0 Then
	' nothing for today yet. Put in as order 01 for today
	DoCmd.RunSQL "Insert into IssuedOrders (OrderDate, LastNumber") Values (#" & Date() & "#,1)")
	strOrderNumber = strOrderNumber & "01"
Else
	bytTemp = DLookup ("LastNumber", "IssuedOrders", "OrderDate=#" & Date() & "#")
	bytTemp = bytTemp +1
	if bytTemp > 9 Then
		strOrderNumber = strOrderNumber & bytTemp
	Else
		strOrderNumber = strOrderNumber & "0" & bytTemp
	End if
	DoCmd.RunSQL "Update IssuedOrders Set LastNumber=" & bytTemp & " Where OrderDate = #" & Date() & "#"
End If
DoCmd.SetWarnings True
GetNumber = strOrderNumber
End Function

John

 

[BLBurden]

danvlas, can you look at my code and tell me what's wrong. I am at my wit end.

I am trying to count records per day of input I want to enter 10 or so records on 6-12-03 and have my increment # to count those. Then on the next day 6-13-03. I want to enter 20 or more records and have the increment # start back at 1 and count to what ever. I have a date field and a ID field. And here is my code. The increment part of it works perfect for counting but not resettig back to #1

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intTodaysRecs As Integer
intTodaysRecs = DCount("[ID]", "SIPP DB 06/03 - 12/03", "[Date] = #" &
Date & "#&quot

Forms!SIPPS!ID = DMax("[ID]", "SIPP DB 06/03 - 12/03&quot + 1
If (IsNull(Forms!SIPPS!ID)) Then Forms!SIPPS!ID = 1
End If

Form_BeforeInsert_Exit:
Exit Sub
Form_BeforeInsert_Err:
MsgBox Error$
Resume Form_BeforeInsert_Exit
End Sub

Thanks for your help

BLB
Always Grateful

 

[danvlas]

1. DMax also accepts a 'where condition' argument. Not employing it causes the calculation on the entire table. That is why your numbers are not reset

2. DMax returns Null if no records matching the criteria are found. Since you're using the + operator, you may end up with a null value even though you try to add something to it. Therefore you should force the null to zero in such cases: Nz(Expression, 0)

3. You should use the BeforeUpdate event combined with NewRecord property, not BeforeInsert event. Remember that the record is not saved - therefore the value, even though calculated already, does not exist in the table. Another user might start adding a record and would get the same number. BeforeUpdate reduces this risk considerably-but does not eliminate it.
The NewRecord property should be checked so that the existing number is not overwritten when you cahange something in the record.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not NewRecord Then Exit Sub
Forms!SIPPS!ID = Nz(DMax("[ID]", "SIPP DB 06/03 - 12/03", "[Date] = #" & Date & "#"),0) + 1

End Sub

For an 'error-free' continuous numbering, search for Michael Red's FAQ on the subject - if you use DAO, that could be what you need.

Good luck,



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[BLBurden]

Thank you so very much, I will let you know and give you a star when and If I get this to work for me.

BLB
Always Grateful