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
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!
I pasted it into the criteria line and I get an expression error
The error is
The expression you entered has an invalid .(dot) or ! operator or invalid parentheses.
You may have entered an invalid identifier or typed parentheses following the Null constant
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
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.
Worked perfect for me.
Thank you Horacio.