Query Criteria using specific Month

[mikejerez]

I am trying to setup a query with the following criteria:

I have a FromDate field and a ToDate field and I want all the records either the from or the to date fall on or through the 15th of the current month.

Example:
FromDate ToDate Include
13 Aug 16 Aug Yes (15th falls between dates)
15 Aug 20 Aug Yes (15th falls between dates)
12 Aug 14 Aug No (15th not in range of dates)
16 Aug 20 Aug No (15th not in range of dates)
10 Aug 15 Aug Yes (15 falls between dates)

 

[raskew]

Hi -

WHERE day(FromDate)<=15 and day(ToDate)>=15

HTH - Bob

 

[mikejerez]

I see, but I want to make sure that it will use the 15th of the current month as the criteria regardless of which month I am.

Example:
If today is 20 November then it will use 15 Nov as the criteria.
If today is 17 Oct it will use 15 Oct as the criteria, etc...

 

[mikejerez]

I see but I need it to use the 15th of the current month as the criteria regardless of the month.

If today is the 20th of November it should use 15 Nov as the criteria, If today is 27 Dec it should use 15 Dec as the criteria, etc....

 

[raskew]

Criteria for FromDate
Between DateSerial(year(date), month(date), 1) and DateSerial(year(date), month(date), 15)

Criteria for ToDate
Between DateSerial(year(date), month(date), 15) and DateSerial(year(date), month(date)+1, 0)


HTH - Bob