Help with DCount expressions in macros

[Neologic29]

Hello,

I am so grateful for the help I received with my last question and I have another, possibly more straightforward one. I have a report which I am using the DCount function on a control to return the number of records for a particular query based on criteria for two of the fields. The expression works perfectly on the report but I am trying to use the same expression in an If statement to check and see if there are records and if not, a message pops up saying something like "No records exist" However, when I try to save the macro, the exact same expression (which I copied and pasted from the report control) is unable to be parsed.


Here is the expression as I tried to enter it (as part of an If/Then statement):

DCount("[Lab Receipt Number]","qryTotal_Tasks_byExaminer","[Examiner]='" & [Forms]![Report Select Form]![cboExaminer] & " ' AND [Date of Report] >= #" & [Forms]![Report Select Form]![txtStartDate] & "# AND [Date of Report]<= #" & [Forms]![Report Select Form]![txtEndDate] & "#")=0

Is it too long for Access to parse? When I remove the Examiner criteria, it seems to be fine. Maybe there is some VBA code that can do this more easily, but I have used this method for other queries and it works great, although the ones that work only have a date criterion. Any help would be appreciated.

[John_G]

Hi -

Are you getting an error message of some kind, or does it just not work?

This may or may not be significant, but you have a space before the closing single quote of the Examiner string in the criteria part of the DCount:

& " ' AND [Date of Report]
^

The ^ marks the extra space.

See if it works if you remove that space.

HTH

John

[Neologic29]

When I attempt to save the macro with that expression, I get a message saying Access cannot parse it. I've tried removing the spaces and that doesn't seem to be the problem. I'll keep working on it and maybe I'll check back here later. Thanks.