I am trying to build an expression in an access table that has a field called WO#. I want it to assign numbers automatically. The format for the number is as follows: 8- 78-0001 (where 8 is the year, 78 is the julian, 0001 is a sequential number). We need the Julian date to automatically reset each day using the PC clock and the sequential number to reset to 0001 each day as well. Any help or advice would be greatly appreciated....
-
-
What you're going to need to do is write a function that will return the number that you're looking for.
I use the same thing for a Batch Number I've created for use in identifying the person as well as the date and the sequence that the number falls under.
In your case, what you're going to need to do is the following:
Create an empty Module
Under Tools -> Reference, Make sure that you've select 'Microsoft DAO 3.x'
[CODE=VB]
Function Seq_No() as String
Dim MyDB as DAO.Database
Dim MyRS as DAO.Recordset
Dim TheYear as String, TheJulian as String, TheSeqNo as String
Set MyDB = CurrentDB()
Set MyRS = MyDB.OpenRecord set("SELECT * FROM [TableName]",dbopendynaset )
'you'll need to create a table that has a Two fields Seq_Date as Date/Time,
'and SEQ_No as Number
'Then you'll save that table and place the name inplace of [TableName]
'this will pull that values that you have in that table so you know what the
'next value is going to be.
TheYear = Right(Str(Year( date())),2)
TheJulian = DateDiff("d",Da teSerial(Year(D ate()),1,1),Dat e())
If MyRS!Seq_Date < Date() Then
MyRS.Edit
MyRS!Seq_Date = Date()
MyRS!Seq_no = 1
Else
MyRS.Edit
MyRS!Seq_no = MyRS!Seq_no + 1
End If
MyRS.Update
TheSeqNo = Right("0000" & MyRS!Seq_No,4)
Seq_No = TheYear & "-" & TheJulian & "-" & TheSeqNo
MyRS.Close
MyDB.close
Set MyRS = Nothing
Set MyDB = Nothing
End Function
[/Code]
Comment