Using Current Date to find 16th

[allenjasonbrown@gmail.com]

Hello - I have a few queries that use date parameters. We use a 15th date as month end.
Example Month - 05/16/2011 - 06/15/2011 (I usually just do a less than 06/16/2011 to make it easy).

I have created these to figure out the 16th of the current month and the 16th of previous month:
MONTH BEGIN: DateSerial(Year(Date()),Month(Date())-1,1)+15
MONTH END: DateSerial(Year(Date()),Month(Date()),1)+15

This will work great assuming the query runs between 06/16/2011 and 06/31/2011.

MY QUESTION:
Is there a way to create an IF statement and between the 16th of current month and 15th of next month, find the correct dates.

FOR EXAMPLE -
IF RUN TODAY I WANT - 05/16/2011 and 06/16/2011
IF I RUN TOMORROW I WANT - 05/16/2011 and 06/16/2011
IF I RUN 07/10/2011 I WANT - 05/16/2011 and 06/16/2011
BUT....IF I RUN 07/16/2011 I WANT - 06/16/2011 and 07/16/2011

Thanks in advance

[jzwp11]

Perhaps this:

WHERE yourdatefield BETWEEN (IIF(day(date())>=16, dateserial(year(dateadd("m",-1,date())),month(dateadd("m",-1,date())),16), dateserial(year(dateadd("m",-2,date())),month(dateadd("m",-2,date())),16))) AND (IIF(day(date())>=16, dateserial(year(date()),month(date()),15), dateserial(year(dateadd("m",-1,date())),month(dateadd("m",-1,date())),15)))

[allenjasonbrown@gmail.com]

That seems too work great.....THANK YOU! (Ill do some more testing on the 1st).

[jzwp11]

You're welcome. It is a little messy but functioned properly with some testing I did.