• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 37
  • Last Modified:

Help with DCount Syntax

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!"
1 Solution
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Try this:
If DCount("*", "tCl", "CL = """ & sFindODBrand & """ And DiscoDiag < Now()") > 0 Then MsgBox "BAM!"

Open in new window

Bill PrewCommented:
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

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!"
thandelAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now