error with multiple dlookup criteria

Question

This code produces a runtime error 3464 for dlookup with matchstr_t. I can't understand why as matchstr_v seems to work fine. Any offers? At the moment all I'm trying to do is use the dlookup to flag duplicate start times for appointments.

Dim i, j, clientid, therid As Integer
Dim origin_date, apptdate  As Date
Dim slotday, apptype, venue, matchstr_v, matchstr_t As String
Dim slottime As Date
Dim appt(25) As Date
Dim db As Database
Dim rst As Recordset
Dim test As Variant

origin_date = Date
slotday = Me.slot_day.Value
slottime = Me.slot_time.Value
clientid = Me.Client_ID.Value
therid = Me.Therapist_ID.Value
venue = Me.venue.Value
apptype = "continuation"

slottime = Format(slottime, "Short Time")
For i = 1 To 7
    apptdate = Date + i
    If Weekday(apptdate, 2) = slotday Then
    'set up stuff
    For j = 0 To 25
        appt(j) = apptdate + (j * 7)

        Set db = CurrentDb()
        Set rst = db.OpenRecordset("Dummy")

        Debug.Print "[therapist ID] = " & therid
        matchstr_v = "[appt date]= #" & appt(j) & "# AND [appt time] = #" & slottime & "# AND [venue] = '" & venue & "'"
        matchstr_t = "[appt date]= #" & appt(j) & "# AND [appt time] = #" & slottime & "# AND [therapist ID] = " & therid
        Debug.Print matchstr_t
        If Not IsNull(DLookup("[dummy ID]", "Dummy", matchstr_v)) Then
            ' do more stuff
        ElseIf Not IsNull(DLookup("[dummy ID]", "Dummy", matchstr_t)) Then
            ' do more stuff
        Else
            rst.AddNew
            rst.Fields("appt date") = appt(j)
            rst.Fields("appt time") = slottime
            rst.Fields("Client ID") = clientid
            rst.Fields("Therapist ID") = therid
            rst.Fields("appt type") = "continuation"
            rst.Fields("attendance") = "scheduled"
            rst.Fields("venue") = venue
            'Debug.Print rst.Fields("appt date")
            rst.Update
            test = (DLookup("[dummy ID]", "Dummy", matchstr_v))
            Debug.Print test
        End If
        rst.Close
        db.Close
    Next j
    Else
    End If
Next i

Please show us what Debug.Print matchstr_t gives you when the error occurs with DLookup. — HansUp
[appt date]= #27/05/2013# AND [appt time] = #16:00# AND [Therapist ID] = 2 — user2405097
What happens when you create and test this new Access query? ... SELECT * FROM Dummy WHERE [appt date]= #27/05/2013# AND [appt time] = #16:00# AND [Therapist ID] = 2 ... you could get error #3464, "Data type mismatch in criteria expression" if the data type of [Therapist ID] is text instead of numeric. — HansUp
Brilliant. Many thanks - [Therapist ID] data type is 'number' in the original table, but when I created dummy to play around with this chunck of code I wasn't careful enough setting data types. Problem solved. I'll work out how to accept this answer/comment. — user2405097

Ted Ted · Accepted Answer · 2013-05-21T11:10:38

What is the datatype of [therapist ID]?

There seems to be a datatype mismatch with the value of therid.

error with multiple dlookup criteria

2 Answers