How to Query if Date is Current Month

[BrianFawcett]

I have a query which I use to generate a report. I want to return all records where the Closed Date is in the Current Month. I know that I can setup a form where I put in the date range and use that to pull the records.

But I want to know if there is a way to write a statement that says If Date field is in Current Month. Can anyone help me with this?

 

[pbaldy]

You can either use the Month() and Year() functions or a Between clause with the two appropriate functions from here:

http://www.pacificdb.com.au/Support/code_date_monthfunctions.htm

which is what I would probably do.

 

[BrianFawcett]

Paul, How does that work when the field I am trying to query off of (Closed Month), is in the format mm/dd/yyyy?

 

[pbaldy]

As long as the data type is Date/Time, either method will work fine.

 

[BrianFawcett]

Paul, I entered this string in my criteria expression on the query builder and when I tried to view the results Access asked me for a parameter value, can you tell me what I did incorrectly?

Date Processed between [myDate1 = Day(DateSerial(Year(dte), Month(dte), 1))] and [myDate2 = Day(DateSerial(Year(dte), Month(dte) +1, 0)]

 

[pbaldy]

Between DateSerial(Year(Date()), Month(Date()), 1) AND DateSerial(Year(Date()), Month(Date()) + 1, 0)

 

[BrianFawcett]

I got a syntax error, "You may have entered a comma without a preceding value or identifier."

 

[Brianwarnock]

I can see nothing wrong with what Paul typed, what did you type, copy and paste it into a post

Brian

 

[BrianFawcett]

I got it to work, thanks so much Paul

 

[pbaldy]

Happy to help!

 

[DCrake]

One thing to consider, What happens if you want to run the report for the current month and the last day of the month falls on a weekend. When you come in on a Monday morning then it becomes the new month. You would not be able to get a statement for the previous month. You may need to think about the last completed month, and/or month to date filters.