I need an expression to find certain date ranges from today

[NaomiC]

Hello again,

I have a column to show the CBT EXPIRES date and I need to query only the records that will expire between today and the last day of the following month. For example Today to 2/28/15 and I need it to automatically update once January has past to include the last day of March and so on. Is there an expression I can enter in the criteria field of the query design view that will return this data?



Thanks!

[June7]

Usually the requirement is for a number of days (30, 60, 90, etc) from current date. You want month end regardless of what current date is?

Maybe:

WHERE [ExpireDate] BETWEEN Date() AND DateSerial(Year(Date()), Month(Date())+2, 0)

Re: International dates, review http://allenbrowne.com/ser-36.html

[orange]

I think, in plain English, you are asking -- How to identify the date range from Today, up to and including, the last day of the month where the month is Today's Month + 1.

If today is 11 Jan 2015, you would like 11 Jan 2015 to 28 Feb 2015.
If today is 30 March 2015, you would like 30 March 2015 to 30 April 2015.

My test using today 11Jan 2015

?"Between " & Date & " AND " & DateSerial(Year(Date()), Month(Date()) + 2, 0)
Between 11/01/2015 AND 28/02/2015

Note: I am using Canadian Date format dd/mm/yyyy

Try

 Between Date and DateSerial(Year(Date()), Month(Date()) + 2, 0)

[NaomiC]

Yes, you both understand what I need but neither one is working

WHERE [ExpireDate] BETWEEN Date() AND DateSerial(Year(Date()), Month(Date())+2, 0)

does not return any records

and

Between Date and DateSerial(Year(Date()), Month(Date()) + 2, 0)

gives me an error : Data
type mismatch in criteria expression. (Error 3464)

I am using US date format, does this effect the how the expression is written?

[orange]

You will have to show us exactly what you have tried.
My expression, and June's, is simply the essence of the where clause involved.

You will need something along this

SELECT * from YourTableNameHere
Where  YourDateField Between Date and DateSerial(Year(Date()), Month(Date()) + 2, 0)

We are expecting you to build the proper sql with the expressions suggested.

[NaomiC]

Thanks Orange and June7!

I did some more "self-eduction" on what SQL is and how to use it. When I went into the SQL view and copy/pasted your suggestions I was getting error messages so I tried to retype it and it worked. I even tested it by changing the +2 to +3 to include March in my search. It worked beautifully so I changed it back to what I actually needed.

Thanks again!