I'm in the process of streamlining the process to maintain our various access reports and queries.
My company's accounting software stores all record dates in Gregorian Serial format (don't ask me why). Currently for our reports we have a local hard-coded table that is manually updated periodically with dates in MM/DD/YYYY format and the coresponding Gregorian serial date number. To go from date to serial is a fairly straightforward access function of:
For example if [Date to convert] was 2/7/2006 the output would be 732349. That's all well and good, however I am in need of a query I can build that will on the fly generate a list from say 1/1/1997 through 1 year after day being run just to be safe with two columns, one with date and one with the serial date equivelant so I can link to the accounting tables for date restrictions.
This way I just have to have the equation in one query instead of a hard coded table or do the calculation in every form or query that needs to look up these tables.
Unfortuantely I've been staring at accounting data (and am not an accountant) way too long and my brain is fried, suggestions?
My company's accounting software stores all record dates in Gregorian Serial format (don't ask me why). Currently for our reports we have a local hard-coded table that is manually updated periodically with dates in MM/DD/YYYY format and the coresponding Gregorian serial date number. To go from date to serial is a fairly straightforward access function of:
Code:
(Datediff('d','1/1/2000',[Date to convert])+730120)This way I just have to have the equation in one query instead of a hard coded table or do the calculation in every form or query that needs to look up these tables.
Unfortuantely I've been staring at accounting data (and am not an accountant) way too long and my brain is fried, suggestions?
