Solved

DCount syntax with inner join

Posted on 2013-11-13
3
1,495 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

776 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