Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Viewing next month in a query

    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...

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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)<12)) or
    ((Month(Date()) = 12) and (Year(EventDate)= Year(Date())+1) and (Month(EventDate)=1))

    This looks like this in the query grid:
    NextMonth.gif


    If by next month, you just mean the next 30 or so days, it is much simpler.

    (EventDate >=Date()) and (eventDate <Date() +31)
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •