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

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 93
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
 
DanielPineault
post Feb 26 2019, 08:23 PM
Post#2


UtterAccess VIP
Posts: 6,961
Joined: 30-June 11



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

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
waitingroomz
post Feb 26 2019, 08:53 PM
Post#3



Posts: 93
Joined: 29-October 18



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

What is the difference between the two ways of declaring?
Go to the top of the page
 
DanielPineault
post Feb 26 2019, 09:20 PM
Post#4


UtterAccess VIP
Posts: 6,961
Joined: 30-June 11



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




--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
waitingroomz
post Feb 27 2019, 09:30 AM
Post#5



Posts: 93
Joined: 29-October 18



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 & "'"))
Go to the top of the page
 
DanielPineault
post Feb 27 2019, 10:14 AM
Post#6


UtterAccess VIP
Posts: 6,961
Joined: 30-June 11



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




--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 12:52 AM