Viewing next month in a query

Topic

New Reply

I have a query with a date field. In the query I would like to somehow show only the next months worth of data. So the query will always display next months data. Any thoughts? Thanks…

Reply | Quote

Replies

It depends what you mean by the Next Month’s data.

So it is now March (as it is) you only want to see the data from April? And if it is December, you want to see results from January next year?

The Month function returns the Month of a Date, so if your field is called EventDate, Month(EventDate) returns the Month of the EventDate.
Date() returns the current date.

So you need records where the Month(EventDate) is one more than Month(Date()), but the years are the same.
Except in December, you need records where Month(eventDate) is 1, but the year is one more than the current date.

So the where clause of your query would be:

((Month(EventDate) = Month(Date()) +1) and (Year(EventDate)= Year(Date())) and (Month(EventDate)=Date()) and (eventDate <Date() +31)

Reply | Quote