Solved

Proper Dcount Syntax

Posted on 2013-11-13
2
335 Views
Last Modified: 2013-11-13
I have the following rowsource in a list box pulling data to display... which is working:

SELECT Count(*) FROM Tpatient WHERE (((Tpatient.RecallNumber)=0) And ((Date()-TPatient!LastApmnt)>(TPatient!RecallFreq*30)) And ((TPatient!RecallActive)=-1)) Or (((Date()-TPatient!RecallNotice)>30) And ((Tpatient.RecallFreq)<12) And ((Tpatient.RecallNumber)<4) And ((Date()-TPatient!LastApmnt)>360) And ((TPatient!RecallActive)=-1)) Or (((Date()-TPatient!RecallNotice)>30) And ((Tpatient.RecallFreq)>=12) And ((Tpatient.RecallNumber)=1) And ((TPatient!RecallActive)=-1));

SELECT Count(*) FROM TPatient WHERE (((TPatient.FirstVLetter)=0) AND ((TPatient.RecallActive)=-1) AND ((TPatient.Zip) Is Not Null));

I would like to change each of these to for use in VBA with a DCount to obtain their value.  I am not able to get the syntax correct.

Is this possible and if so what should the proper syntax be for DCount?
0
Comment
Question by:thandel
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39646964
Syntax for the first case:
DCount("*", "Tpatient" , "(((Tpatient.RecallNumber)=0) And ((Date()-TPatient!LastApmnt)>(TPatient!RecallFreq*30)) And ((TPatient!RecallActive)=-1)) Or (((Date()-TPatient!RecallNotice)>30) And ((Tpatient.RecallFreq)<12) And ((Tpatient.RecallNumber)<4) And ((Date()-TPatient!LastApmnt)>360) And ((TPatient!RecallActive)=-1)) Or (((Date()-TPatient!RecallNotice)>30) And ((Tpatient.RecallFreq)>=12) And ((Tpatient.RecallNumber)=1) And ((TPatient!RecallActive)=-1))")

Open in new window


Syntax for the second case:
DCount("*", "TPatient", "(((TPatient.FirstVLetter)=0) AND ((TPatient.RecallActive)=-1) AND ((TPatient.Zip) Is Not Null))")

Open in new window

0
 

Author Closing Comment

by:thandel
ID: 39646973
OK that was pretty straight, not sure why it wasn't working for me... I think I was missing a quote.  Thank you.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now