Dcount records by date

[dark11984]

Can someone give me a hand with this one... Not sure where my speech marks should be?

=DCount("[id]","[qrysso]","[Month]=" & Month(DateAdd("m",-1,Date())) And "[Year]=" & Year(DateAdd("m",-1,Date())))

Cheers

 

[pbaldy]

Try

=DCount("[id]","[qrysso]","[Month]=" & Month(DateAdd("m",-1,Date())) & " And [Year]=" & Year(DateAdd("m",-1,Date())))

 

[dark11984]

No good... get #Error message

 

[pbaldy]

What are the data types of the month and year fields? Those aren't good names by the way, as Access will confuse them with the functions of the same name.

 

[dark11984]

ok i've changed the names of the fields. They are formatted date fields from a query - SSOMonth: Format([SSODate],"mmm") and SSOYear: Format([SSOYear],"yyyy")

 

[pbaldy]

The month field is returning text so the DCount() syntax will need to be adjusted as noted here:

http://www.mvps.org/access/general/gen0018.htm

Probably the same for the year field. Even though it's returning a numeric looking value, the Format() function will return a string value.

 

[dark11984]

Thanks, I've been looking for a link like that to explain the apostrophes however, ive tried a few different variations and am winding up with either #error or 0 as the result.

=DCount("[id]","[qrysso]","[ssoyear]= '" & Year(DateAdd("m",-1,Date())) & "' And [ssomonth]= '" & Month(DateAdd("m",-1,Date())) & "'")

 

[pbaldy]

I think you have a mismatch between these two:

SSOMonth: Format([SSODate],"mmm") which will produce "Sep"

Month(DateAdd("m",-1,Date())) which will produce 9

You need to compare "apples to apples".

 

[dark11984]

Now that makes sense... Got it working. I changed the format in my query to "mm" and here's the code I finished up with.

=DCount("[id]","[qrysso]","[ssoyear]= " & Year(DateAdd("m",-1,Date())) & " And [ssomonth]= " & Month(DateAdd("m",-1,Date())))

Thanks for your help!

 

[pbaldy]

No problem, glad we got it sorted out.