Change date criteria depending on the day of the month the query is run

[ghudson]

This one has me stumped. I am trying to change the date criteria in a select query if the day of the month is the 1st day or not. If it is the 1st day of the month then I want the date criteria to be between the 1st and last day of the previous month. If the current day is not the 1st day of the month then I want the date criteria to be between the 1st day of the current and the last day of the current month. I know how to do pull the between dates ranges but when I use an IIF or Switch the between dates range criteria does not work in a query.

How can I test the day of the month and use one date range if today is the 1st day of the month and use a different date range if today is not the 1st day of the month?

This is what looks like it should work but Access does not like the date ranges inside of a formula like an IIF or Switch. Using IIf(Day(Now())=1 is an easy way to test if today is the 1st day of the month.

IIf(Day(Now())=1,Between DateSerial(Year(Now()),Month(Now())-1,1) And DateSerial(Year(Now()),Month(Now()),0),Between DateSerial(Year(Now()),Month(Now()),1) And DateSerial(Year(Now()),Month(Now())+1,0))

Thank you in advance for your help!

 

[spikepl]

Go back to basics.

(Day(Date())=1 AND X BETWEEN B AND C) OR (NOT Day(Date())=1 AND X BETWEEN E AND F)

 

[ghudson]

I do not understand the AND X part of your example.

(Day(Date())=1 AND X Between #1/1/2013# And #1/31/2013#) OR (NOT Day(Date())=1 AND X Between #2/1/2013# And #2/28/2013#)

Why does this not work in a query for a date criteria? Separately they work fine.

IIf(Day(Now())=1,Between #1/1/2013# And #1/31/2013#,Between #2/1/2013# And #2/28/2013#)