Go to Top

Criteria for the current month in a query

The other day I needed to create a query where it returns all records with dates for the current month. I did not want to hard code the beginning and ending dates in the query, so I came up with the following line of code you can paste into your query:

Between CDate(Month(Date()) & “/1/” & Year(Date())) And DateAdd(“m”,1,CDate(Month(Date()) & “/1/” & Year(Date())))-1

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

The criteria line above uses the “Between And” operators to calculate the first of the month and the end of the month of the current date. Enjoy!

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He’s a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

2 Responses to "Criteria for the current month in a query"

  • Horacio Madariaga
    June 22, 2015 - 11:48 pm Reply

    Juan,

    Another approach to get all records for the current month would be the following WHERE clause.

    WHERE Format([myDateField],”yyyymm”)= Format(Date(),”yyyymm”)

    Regards,

    Horacio

    • Ben Clothier
      July 24, 2015 - 8:16 pm Reply

      Horacio, while the proposed filter is much simpler to read and understand, unfortunately, it won’t scale well because when you wrap the column in a function, the engine cannot use index to optimize the query. It is forced to evaluate each value in the [myDateField] column to then compare to the formatted Date().

      The approach that Juan used allows index to be used because we calculate the start and end of dates on the right hand and we only need to look at the raw [myDateField], which is also in an index and thus engine can use index instead to match the records.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]