Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DCount syntax with inner join

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
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…

618 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