Solved

DCount syntax with inner join

Posted on 2013-11-13
3
1,392 Views
1 Endorsement
Last Modified: 2013-11-13
I have the following working in a listbox's rowsource, I would like to modify to a DCount.

SELECT Count(*) AS CountOfDOS FROM TPatient LEFT JOIN TClaim ON TPatient.ID=TClaim.ID WHERE TPatient.ID=Forms!FPatientEntryQuery!ID And TClaim.ActiveClaim=True And [DOS]<DateAdd("d",-60,Date());

I am having issues with the quotes.  Can a Dcount be performed as this?  If so what would the proper syntax be?
1
Comment
Question by:thandel
3 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39647043
You will need to create a query that joins TPatient and TClaim.

1. Create a query with this SQL syntax. Call it QPatientClaims:
SELECT TPatient.ID AS PatientID, TClaim.ActiveClaim, [DOS] FROM TPatient LEFT JOIN TClaim ON TPatient.ID=TClaim.ID;

Open in new window


2. Your DCount syntax will be:
DCount("*", "QPatientClaims", "PatientID=" & CStr(Forms!FPatientEntryQuery!ID) & " And ActiveClaim=True And [DOS]<DateAdd(""d"",-60,Date())")

Open in new window

0
 

Author Closing Comment

by:thandel
ID: 39647058
Thank you.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39647069
An alternative to DCount:

Dim iCnt as integer

iCnt = CurrentDb.OpenRecordset("SELECT Count(*) AS CountOfDOS FROM TPatient LEFT JOIN TClaim ON TPatient.ID=TClaim.ID WHERE TPatient.ID=Forms!FPatientEntryQuery!ID And TClaim.ActiveClaim=True And [DOS]<DateAdd('d',-60,Date())").Field(0)
Msgbox iCnt

Open in new window

Or you could use the specify the rowsource of your control (although it seems unusual that that your listbox rowsource only returns a single row showing the count).
iCnt = CurrentDb.OpenRecordset(Me.Listbox.Rowsource).Field(0)

Open in new window


Ron
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 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

13 Experts available now in Live!

Get 1:1 Help Now