Using DCount with WHERE criteria

[HeislerKurt]

My main form holds patient information (tblPatients), and my subform
holds daily information (tblDailyData) collected for each patient.
(Hence, a one-to-many.)

An individual patient should not have the same date entered twice, so
on the subform, in the BeforeUpdate event of the date control, I need
to check for a duplicate date. Right now my code looks like:

If DCount("DailyDate", "tblDailyData", "DailyDate= #" & Me.DailyDate &
"#") > 0
Then
Me.Undo
MsgBox "Sorry! This date has already been entered.",
vbExclamation, "Duplicate Date"
End If

But obviously this just checks for duplicate dates in tblDailyData.
Instead, I need to check for a duplicate date WHERE PatientID =
Me.PatientID.

Do I need to rewrite this with a SQL string, or can DCount handle
something like:

If "tblPatients.PtID, DCount('DailyDate', 'tblDailyData', 'DailyDate=
#' & Me.DailyDate & '#') > 0 " & _
"From tblPatients " & _
"INNERJOIN tblDailyData " & _
"ON tblPatients.PtID = tblDailyData.PtID " & _
"WHERE tblPatients.PtID=" & Me.PtID Then

The above code gives me type mismatch error.

Thank you for any pointers.

Kurt

 

[Allen Browne]

Hi Kurt

You can use 2 fields in the Criteria of a lookup like this:

Dim strWhere As String

If Not (IsNull(Me.PatientID) OR IsNull(Me.DailyDate)) Then
strWhere = "(PatientID = " & Me.PatientID & ") AND (DailyDate = " & _
Format(Me.DailyDate, "\#mm\/dd\/yyyy\#") & ")"
If Not IsNull(DLookup("DailyDate", "tblDailyData", strWhere)) Then
...

DLookup() returns a value if there is a match, or Null if there is none.
That may be more efficient than counting the number of matches.

 

[John W. Vinson]

Do I need to rewrite this with a SQL string, or can DCount handle
something like:

Well, no, not like that - most of your SELECT is not even inside the DCount!

The third argument to DCount is a text string which evaluates to a valid SQL
WHERE clause, without the WHERE keyword. So:

If DCount("DailyDate", "tblDailyData", "DailyDate= #" & Me.DailyDate &
"# AND PtID = " & Me!PtID) > 0
Then
Me.Undo
MsgBox "Sorry! This date has already been entered.",
vbExclamation, "Duplicate Date"
End If


John W. Vinson [MVP]

 

[HeislerKurt]

Both methods above worked great. Thank you!

Kurt