Query Criteria to give a date range (1 Viewer)

Hi Guys,

This is probably a simple syntax issue for me but I am struggling to find an answer.
I am querying a table which contains 3000+ records which include a date field with an expiry date.
I would like an expression to filter expired dates including Null values and all records with an expiry date due up to and including the end of the following month.

I have had some success using the following:
The problem is I have to keep manually changing the value in the section (39 in the example) in order to get the number of days from today up to the end of the following month. I have tried using but it does not include all records up to the end of the month.
Is there an expression that will give me all records up to the end of the following month automatically regardless of when I run the query?

Any help gratefully received

DateSerial should give you the end of a specific month.

Like Tieval says, you can use DateSerial. The tricky bit is determining the last day of the month.

Take a look at this:


The idea here is you add two months to the current date to determine the year and month after the one you want. Then use these along with a value of zero for day in the DateSerial function. Using zero (0) as the day value gives you the date for the last day of the previous month (which is the last day of the month we want).

hth

stopher said:

Like Tieval says, you can use DateSerial. The tricky bit is determining the last day of the month.

Take a look at this:

Code:Copy to clipboard

DateSerial(Year(DateAdd("m",2,date())),Month(DateAdd("m",2,date())),0)

The idea here is you add two months to the current date to determine the year and month after the one you want. Then use these along with a value of zero for day in the DateSerial function. Using zero (0) as the day value gives you the date for the last day of the previous month (which is the last day of the month we want).

hth

Click to expand...

Hi stopher,
Thank you so much for your excellent explanation, this returns the values I was looking for.
No wonder I was struggling, I would not have come up with that. I wish I could think like a programmer but I find it hard as a newbie to access to think laterally in order to solve issues like this.

Thanks also to Tieval for pointing me in the right direction