Autocreate Tracking number based on date

[starmistress]

I give up. I'm trying to use the expression builder to create an autonumber, but I can't get the syntax right. What I want is to create a tracking number that begins with the last two digits of the year (06) then concatenates the two digit month (07) then concatenates an autonumbered field 0-filled to 4 characters. For instance, 06070001 would be a valid tracking number with every entry created this month beginning with 0607. The last four digits would not need to start over every month. I tried to do this in code, but the field name that holds the autonumber wouldn't carry over, I tried doing this in the expression builder, but it didn't like me using 'date'. So, I give.

Does any of this make any sense?

 

[Golom]

An autonumber field isn't appropriate here. You will probably need to create a text field because of the requirement to have a leading zero in the value. You will then want to create the next tracking number with code of the form

Dim TrackNum As String
Dim SQL as String
Dim rs As DAO.Recordset
Dim n As Long
TrackNum = Format ( Date(), "yymm" )
SQL = "Select MAX(TrackingNumber) As MX " & _
      "From myTable " & _
      "Where Left(TrackingNumber,4) = '" & TrackNum & "'"
Set rs = CurrentDB.OpenRecordSet ( SQL )
If IsNull(rs![MX]) Then
   n = 1
Else
   n = Val(rs![MX],4)
End If
TrackNum = TrackNum & Format ( n, "0000" )

SQL = "INSERT INTO myTable ( TrackingNumber, ... ) " & _
      "VALUES ( '" & TrackNum & "'," ...
Currentdb.Execute SQL

 

[NvrSaDi]

Assunming you are running an Access query, try Right(Year(Now()),2) for the 2-digit year and Month(Now()) for the month number. In Access 2000, query will accept "Now()" function, but not "Date()" function. You can then concatenate with an &. Since the month number will give you either single or double digits numbers, you will have to add an If statement that adds a 0 in front of the month number if Len() function = 1. The Len() function counts number of digits in the month number.

As for the last 4 autonumber digits, there are several possible approaches. If you are adding individual records to a table, you can set up an autonumber field in the table and concatenate the results to the end of your year and month. Again, you would have to use a Len() function to determine how many zeros you would want to append to the front of the autonumber.

An alternative would be to create a calculated field that calls a public function in a module. You can then write a VBA procedure in the module that will track the highest number in a table using a DMAX() function, add a value of 1 and then append zeros and return the function value to the query.

I would be interested in knowing if anybody has an easier way to create autonumbers within a query.

 

[starmistress]

Thanks everyone, I got it1

 

[MichaelRed]

Generating Id "Numbers" is fraught with issues, more particularly in the multiuser environment. Ms. A. (Jet) doen not have record level locking, which opens the door for 'simultaneous' retrievial of values from tables --> non-unique Id values, which is a bad thing.

see faq700-184



MichaelRed