?
Solved

DCount syntax with inner join

Posted on 2013-11-13
3
Medium Priority
?
1,705 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

752 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