By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Access 2003 Expression

P: 63
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....
Mar 18 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 374
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'


Expand|Select|Wrap|Line Numbers
  1. Function Seq_No() as String
  2.      Dim MyDB as DAO.Database
  3.      Dim MyRS as DAO.Recordset
  4.      Dim TheYear as String, TheJulian as String, TheSeqNo as String
  5.      Set MyDB = CurrentDB()
  6.      Set MyRS = MyDB.OpenRecordset("SELECT * FROM [TableName]",dbopendynaset)
  7.  
  8.      'you'll need to create a table that has a Two fields Seq_Date as Date/Time,
  9.      'and SEQ_No as Number
  10.      'Then you'll save that table and place the name inplace of [TableName]
  11.      'this will pull that values that you have in that table so you know what the 
  12.      'next value is going to be.
  13.  
  14.      TheYear = Right(Str(Year(date())),2)
  15.      TheJulian = DateDiff("d",DateSerial(Year(Date()),1,1),Date())
  16.      If MyRS!Seq_Date < Date() Then
  17.                MyRS.Edit
  18.                MyRS!Seq_Date = Date()
  19.                MyRS!Seq_no = 1
  20.      Else
  21.                MyRS.Edit
  22.                MyRS!Seq_no = MyRS!Seq_no + 1
  23.      End If
  24.      MyRS.Update
  25.      TheSeqNo = Right("0000" & MyRS!Seq_No,4)
  26.      Seq_No = TheYear & "-" & TheJulian & "-" & TheSeqNo
  27. MyRS.Close
  28. MyDB.close
  29.  
  30. Set MyRS = Nothing
  31. Set MyDB = Nothing
  32. End Function
  33.  
  34.  
Mar 19 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.