We are using a date code to help facilitate FIFO inventory control. When a user performs a transaction, we need the date code automatically generated in the DateCode field of the form. Date code consists of, for the current date, :
Year = A single alpha character
2011 = A, 2012 = B, 2013 = C, etc.
Month = A sigle alpha character
Jan = A, Feb = B, Mar = C, etc.
Day = Two character representation
1 = 01, 2 = 02 - 10=10, etc.
Example: March 8, 2012 = BC08
- Follow via:
- RSS
- Email Alert
Question
0
Votes
How do I automate generation of a Date Code from =Date() in MS-Access 2007?
26th Nov 2011
Answers (1)
-1
Votes
A better question would be why bother, but
you could do it with a calculated field, it's jus a big bunch of vb (or sql functions)
So if the date you are building the code from is TransactionDate
e.g.
Year(TransactionDate) = 2012
2012 - 2011 = 1
A is ascii 65
B is chr(65 + 1)
Ad nauseum.
So in VB it's Year, Month and Day (I think), In SQL it's DatePart for the parts of your date
Chr in SQl would be a convert(Char,SomeValue)
So if the date you are building the code from is TransactionDate
e.g.
Year(TransactionDate) = 2012
2012 - 2011 = 1
A is ascii 65
B is chr(65 + 1)
Ad nauseum.
So in VB it's Year, Month and Day (I think), In SQL it's DatePart for the parts of your date
Chr in SQl would be a convert(Char,SomeValue)
26th Nov 2011
