[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DCount syntax with inner join

Posted on 2013-11-13
3
Medium Priority
?
1,843 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 25

Accepted Solution

by:
chaau earned 2000 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

873 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