Help with DCount Syntax

thandel
thandel used Ask the Experts™
on
I am using Dcount to look if a field (CL - String) in a table has a particular date of another field (DiscoDiag - Date) in MS Access 2003.

I'm testing with the following VBA line but getting an error.  I believe its just my syntax or placement of quotes.  I want to put up a message when the date of DiscoDiag is after todays date.  Any help is appreciated.

If DCount("*", "tCl", "CL = """ & sFindODBrand & """ And DiscoDiag < Now()""") > 0 Then MsgBox "BAM!"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this:
If DCount("*", "tCl", "CL = """ & sFindODBrand & """ And DiscoDiag < Now()") > 0 Then MsgBox "BAM!"

Open in new window

Test your restores, not your backups...
Top Expert 2016
Commented:
Personal preference, I like to use single quotes in the SQL part of the clauses.  I find it clearer...

If DCount("*", "tCl", "CL = '" & sFindODBrand & "' And DiscoDiag < Now()") > 0 Then MsgBox "BAM!"

Open in new window


»bp
Distinguished Expert 2017

Commented:
You didn't tell us what the error message was.   The best technique for  creating strings like this Where clause is to build it into a variable so that you can view the completed string when you get an error.

Dim strWHERE as string
strWHERE = "CL = """ & sFindODBrand & """ And DiscoDiag < Now()"""

If DCount("*", "tCl", strWHERE) > 0 Then MsgBox "BAM!"

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial