Using DCount with WHERE criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HeislerKurt@gmail.com - 05 Jul 2007 01:30 GMT

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

Reply to this Message

Allen Browne - 05 Jul 2007 02:05 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> My main form holds patient information (tblPatients), and my subform
> holds daily information (tblDailyData) collected for each patient.
[quoted text clipped - 31 lines]
>
> Kurt

Reply to this Message

John W. Vinson - 05 Jul 2007 03:09 GMT

>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]

Reply to this Message

HeislerKurt@gmail.com - 05 Jul 2007 17:43 GMT

Both methods above worked great. Thank you!

Kurt

On Jul 4, 10:09 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

> >Do I need to rewrite this with a SQL string, or can DCount handle
> >something like:
[quoted text clipped - 13 lines]
>
> John W. Vinson [MVP]

Reply to this Message