UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Dlookup Multi Criteria (date And Id), Access 2013    
 
   
waitingroomz
post Feb 26 2019, 02:19 PM
Post#1



Posts: 108
Joined: 29-October 18



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?
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    29th January 2020 - 04:13 AM