Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Searching + Data Mining _ Dlookup Multi Criteria (date And Id)

Posted by: waitingroomz Feb 26 2019, 02:19 PM

I have the following that is used to match a screening to a treatment:

CODE
Dim strDate1, strDate2 As Date

If Not IsNull(DMax("ScreeningDate", "tblScreenings", "[PatientID]=" & Nz(Me.OpenArgs, strPatient))) Then
            strDate1 = DMax("ScreeningDate", "tblScreenings", "[PatientID]=" & Nz(Me.OpenArgs, strPatient))
            strMsg = CStr("Screened on " & strDate1 & "?")
            If MsgBox(strMsg, vbYesNo, "Verify Screening Date") = vbYes Then
                strVar = DMax("ID", "tblScreenings", "[PatientID]=" & Nz(Me.OpenArgs, strPatient))
                Me.cboAssociatedScreening.DefaultValue = strVar
            Else
                If MsgBox("Has this patient had multiple screenings?", vbYesNo, "Looking for Screening...") = vbYes Then
                    If Not IsNull(DLookup("ScreeningDate", "tblScreenings", "ScreeningDate>#" & strDate1 & "#" And "[PatientID]='" & Nz(Me.OpenArgs, strPatient & "'"))) Then
                        strDate2 = DLookup("ScreeningDate", "tblScreenings", "ScreeningDate>#" & strDate1 & "#" And "[PatientID]='" & Nz(Me.OpenArgs, strPatient & "'"))
                        strMsg = CStr("Screened on " & strDate2 & "?")
                        If MsgBox(strMsg, vbYesNo, "Verify Screening Date") = vbYes Then
                            strVar = DLookup("ID", "tblScreenings", "[ScreeningDate]=#" & strDate2 & "#")
                            Me.cboAssociatedScreening.DefaultValue = strVar
                        Else
                            MsgBox "No Screening Profile could be found to match.", vbExclamation, "Missing Screening Profile"
                        End If
                    Else
                        MsgBox "No Screening Profile could be found to match.", vbExclamation, "Missing Screening Profile"
                    End If
                Else
                MsgBox "No Screening Profile could be found to match.", vbExclamation, "Missing Screening Profile"
                End If
            End If
        End If


I need to use strDate1 to see if there is a record with a ScreeningDate earlier than the first one it found. When I get to the multiple screenings [vbYes], I am returning a Type Mismatch error#13.

What am I missing?

Posted by: DanielPineault Feb 26 2019, 08:23 PM

Which line exactly?
What is strDate1 returning as a value?
What is strPatient returning as a value?



You should fully declare each variable

Dim strDate1 As Date
Dim strDate2 As Date

Posted by: waitingroomz Feb 26 2019, 08:53 PM

strDate1 and strDate2 returns a date field
strPatient returns the PK Autonumber

What is the difference between the two ways of declaring?

Posted by: DanielPineault Feb 26 2019, 09:20 PM

In your format, you are effectively not specifying the data type for the first variable strDate1.

To illustrate, you can simply compare the following

CODE
Sub Test1()
'0 -> vbEmpty
'7 -> vbDate
Dim strDate1, strDate2 As Date
Debug.Print VarType(strDate1), VarType(strDate2)
'Returns 0, 7
End Sub

Sub Test2()
Dim strDate1 As Date, strDate2 As Date
Debug.Print VarType(strDate1), VarType(strDate2)
'Returns 7, 7
End Sub


So you should do either

CODE
Dim strDate1 As Date, strDate2 As Date


or

CODE
Dim strDate1 As Date
Dim strDate2 As Date




Posted by: waitingroomz Feb 27 2019, 09:30 AM

Ah, thank you!

This is good information!

Any idea about the line?:

CODE
strDate2 = DLookup("ScreeningDate", "tblScreenings", "ScreeningDate>#" & strDate1 & "#" And "[PatientID]='" & Nz(Me.OpenArgs, strPatient & "'"))

Posted by: DanielPineault Feb 27 2019, 10:14 AM

if PatientID is a PK, you don't need the surround single quote (which is for textual values, not numeric ones).

CODE
strDate2 = DLookup("ScreeningDate", "tblScreenings", "ScreeningDate>#" & strDate1 & "# And [PatientID]=" & Nz(Me.OpenArgs, strPatient))