• 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!"
0
thandel
Asked:
thandel
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

0
 
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


ยปbp
0
 
PatHartmanCommented:
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!"
0
 
thandelAuthor Commented:
Thanks!
0
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