Help with Dcount 1

[Sydney1]

Hi,

I'm trying to see if the count of closed records this month is equal to 0. I have the following code"

If DCount("*", "qryMainForm", "Format([completeDate],'mmyyyy')= " & Format(Date(),'mmyyyy') = 0 Then
msgbox "blah"
exit sub
End if

But am getting compile error mesage: expected expression at the first ' at the second mmyyyy.

Any help would be greatly appreciated.

Sydney

 

[PHV]

Replace this:
Format(Date(),'mmyyyy')
By this:
Format(Date(),"mmyyyy")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sydney1]

Hi,

Thanks for the help. After replacing the ' with ", I get a compile error: Expected: list separator or ) at the THEN


Sydney

 

[PHV]

If DCount("*", "qryMainForm", "Format([completeDate],'mmyyyy')= " & Format(Date(),"mmyyyy")) = 0 Then


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BobStubbs]

I think Access is unhappy with the use of Format$ and Date() inside the DCount function - I don't think it can resolve where all the quotes, brackets and delimiters are!

This syntax worked for me - I tried breaking the statement into smaller steps, using a couple of extra variables. This makes the actual DCount statement much simpler:

Dim intResult as Integer
Dim strThisMonth as String

strThisMonth = Format$(Date,"mmyyyy")
intResult = DCount("*", "qryMainForm", "Format([completeDate],'mmyyyy')=" & strThisMonth)

If intResult = 0 then
   etc.
End If

I hope that this will help.


Bob Stubbs

 

[Sydney1]

Didn't get any of the previous stated errors, but got a data type mismatch in criteria expression error when I clicked the button.

I haven't yet grasped the single quotes and double quotes usage.

Again, thanks

Sydney

 

[PHV]

If DCount("*", "qryMainForm", "Format([completeDate],'mmyyyy')= '" & Format(Date(),"mmyyyy") & "'") = 0 Then


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sydney1]

Hi,

That did it! You helped me out of another jam. Thanks.

Also, Thanks Bob for your assistance.

Sydney