Expression DCOUNT with Last Month Date criteria

[Iceolate]

Hi everyone. I have looked on similar posts here and the internet but could not find the answer.
I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax.
Closed: DCount("*","obsvnofilterqry","(Date_Closed)=MONTH(Date())")
Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky. Any help appreciated as always.

 

[Galaxiom]

DateClosed Between DateSerial(Year(Date()), Month(Date()-1),1) And  DateSerial(Year(Date()), Month(Date()),0)

 

[Iceolate]

Thank you for your reply.
I have entered
closedlastmonth: DCount("*","obsvfilterqry","(Date_Closed) Between DateSerial(Year(Date()), Month(Date()-1),1) And DateSerial(Year(Date()), Month(Date()),0")

as as new obsvfilterqry field but I am getting am error of missing brackets or item. Could you review my entry?

Thanks,

 

[CJ_London]

....And DateSerial(Year(Date()), Month(Date()),0)")

 

[Iceolate]

Thanks CJ but I am still getting the same error of missing brackets etc

 

[CJ_London]

the only other thing I can see is

Month(Date()-1)

should be

Month(Date())-1,

but if you are in January then the would produce 0 which is not a valid month

 

[Iceolate]

Thanks CJ for taking another look.
I will try tomorrow. As with many things in Access there are always different ways of achieving the same thing, so I may look at other ways of counting the records in a query within the last month.

 

[Galaxiom]

the only other thing I can see is

Month(Date()-1)

should be

Month(Date())-1,

Yes. My bad. Not awake properly. The expression should be:

DCount("*","obsvfilterqry","Date_Closed Between DateSerial(Year(Date()), Month(Date())-1,1) And DateSerial(Year(Date()), Month(Date()),0")

but if you are in January then the would produce 0 which is not a valid month

DateSerial is smarter than that. It handles any integer as the parameter for the Month or Day arguments and any positive integer for Year so long as the result is a valid Access date.

Using 0 is a very common solution for finding the last month or day of a previous interval.

 

[Iceolate]

Hi Galaxiom,
Thanks for posting. I am still getting the same error..missing bracket etc or item in query expression. I am entering in a new field in the obsvfilterqry closedlastmonth:
DCount("*","obsvfilterqry","Date_Closed Between DateSerial(Year(Date()), Month(Date())-1,1) And DateSerial(Year(Date()), Month(Date()),0")

 

[CJ_London]

see post #4 and compare the bit in red with what you have just posted

 

[Iceolate]

Hi CJ
I tried again and with your and Galaxiom's help managed to get it working.
I have also copied in the solution for "this" months data which works too.
Really appreciate your patience.


closedlastmonth: DCount("*","obsvfilterqry","Date_Closed Between DateSerial(Year(Date()), Month(Date())-1,1) And DateSerial(Year(Date()), Month(Date()),0)")

closedthismonth: DCount("*","obsvfilterqry","Date_Closed Between DateSerial(Year(Date()), Month(Date()),1) And DateSerial(Year(Date()), Month(Date())+1,0)")

 

[Iceolate]

Hi Galaxiom,
With the help of yourself and CJ, I have managed to get it working.
Thanks for your patience, much appreciated.