[X]My Assistant

[waitingroomz]

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?

[DanielPineault]

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

[waitingroomz]

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

What is the difference between the two ways of declaring?

[DanielPineault]

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

[waitingroomz]

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 & "'"))

[DanielPineault]

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