Query Help

[Joe Blow]

OK,

Maybe one of you Access geniuses (MSAMVP) can figure this out...

I have an Access 2000 database linked to a huge Oracle database.
My company's fiscal month is the 22nd of the month to the 21st
of the next month. Currently, it's 01/22/05 to 02/21/05.

I would like to be able to write an expression that I don't have
to change at the beginning of every fiscal month... So instead
of using: >=01/22/2005 and <= 02/21/2005 I would like to
somehow incorporate the "Today()" function to automatically
calculate the current "fiscal" month.

Today's date is mm/dd/yy.

If today is =mm22yy, where dd=22 then
the range should be mm/dd/yymm+1)/21/yy

If today is <mm22yy, where dd<22 then
the range should be (mm-1)/22/yy:mm/21/yy

If today is >mm22yy, where dd>22 then
the range should be mm/22/yymm+1)/21/yy



I've racked my brains, and am quite out of fresh ideas.


Any help would be greatly appreciated.


Joe

 

[Jeff Boyce]

No genius necessary...

One approach might be to use the DateSerial() function and the Month() and
Day() functions, as well as the IIF() function. The following is untested
aircode you could include in a query (your syntax may vary -- mind the word
wrap):

for the FROM value

IIF(Day(Date())>21,DateSerial(Year(Date()),Month(Date()),22),DateSerial(Year
(Date()),Month(Date())-1,22))

for the TO value

IIF(Day(Date())>21,DateSerial(Year(Date()),Month(Date()+1),21),DateSerial(Ye
ar(Date()),Month(Date()),21))

You could use the values these produce in a criterion using the Between xxxx
AND xxxx statement.