Extracting Month and Year For a Given Date

[Jeff Garrison]

I know I've done this before, but for the life of me, I can't remember
how...

I have a fiscal calendar in a table with the fields Month, Year,
MonthBeginDate, and MonthEndDate.

What I need to do is from within a query, be able to get the Year and Month
for the day that is in another query, i.e. let's say that in one query I
have a date of 3/10/07. That date should come back from the Fiscal Calendar
table as being in Month 2 in Year 2007. (The fiscal calendar is offset by 1
month, meaning Feb is Period 1, Mar is Period 2, etc. with Jan being Period
12). I don't want to hard code the dates in the query's criteria for the
simple fact of at the beginning of the next fiscal year, the query criteria
would be wrong.

Do I run this with a query, or do I do it as a lookup?

Thanks

Jeff G

 

[KARL DEWEY]

If your fiscal year is always offset by exactly one month then do not use a
table but the following for fiscal month and year.
Fiscal Month: Format(DateAdd("m",-1,[YourDateField]),"m")
Fiscal Year: Format(DateAdd("m",-1,[YourDateField]),"yyyy")