asked on

SQL Query Syntax in Access VBA Code (Type Mismatch)

I have a table called tblAppointments. Originally, it had two fields, ApptStart and ApptEnd, both as Date/Time and held both the start/end date and time of the appointments stored.

Part of the code to populate a weekly calendar form was to run a query to compile the appointments for the current view out of the table based on a variable called vFirstDate (Dim As Date), which is passed to the sub to calculate the date range to be displayed on the form and capture all appointments that fall within that range. The original code is as follows and worked flawlessly:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAppointments WHERE " _
    & "DateValue(ApptStart) <= #" & Format(vFirstDate + 6, "yyyy/m/d") & "# AND " _
    & "DateValue(ApptEnd) >= #" & Format(vFirstDate, "yyyy/m/d") & "# ORDER BY ApptStart")

Open in new window

For other reasons I had to break out the date field for each entry in tblAppointments. I created a new field in the table called ApptDate (Date/Time), and renamed the other two fields to show they are only for the time value of the appointment as ApptStartTime and ApptEndTime. I updated the query based on this change to be:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAppointments WHERE " _
    & "DateValue(ApptDate) <= #" & Format(vFirstDate + 6, "yyyy/m/d") & "# AND " _
    & "DateValue(ApptDate) >= #" & Format(vFirstDate, "yyyy,m,d") & "# ORDER BY ApptDate, ApptStartTime")

Open in new window

Now I am receiving a "Type Mismatch" error every time it runs. That's it! It doesn't tell me which fields or data are mismatched. When I change it back to the original, it works again <sigh!>.

I've been staring at it all morning. Can someone please tell me what I am doing wrong?? Thank you!

mbizup

Try this... I think your formatting is incorrect:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAppointments WHERE " _
    & "DateValue(ApptDate) <= #" & Format(vFirstDate + 6, "yyyy/m/d") & "# AND " _
    & "DateValue(ApptDate) >= #" & Format(vFirstDate, "yyyy/m/d") & "# ORDER BY ApptDate, ApptStartTime")

Open in new window

Gustav Brock

Maybe your ApptDate contains Null values.

/gustav

JohnMc0620

ASKER

Gustav, thanks for the gut check, but I only have two records in the table at the moment and they both have all date and time fields filled in.

mbizup, I am a little confused... what you posted for me to try is exactly what I posted that didn't work?? Did you accidentally re-post my query instead of one you altered?

Thank you!

mbizup

They aren't the same...

Your query, second date criteria uses:

Format(vFirstDate, "yyyy,m,d")

I changed that in my post to:

Format(vFirstDate, "yyyy/m/d")

JohnMc0620

ASKER

Sorry, I did not see that. I must have typed it incorrectly when typing out the question. I double checked it in my code and I have slashes in for both criteria. Still getting the type mismatch. My apologies for muddying the waters with my poor typing skills!

mbizup

Do you have rst explicitly declared as DAO.Recordset?

Also, what is the exact error message (verbatim) you are receiving ('type mismatch in criteria expression', or something else)?

Can you post the rest of the code for context?

JohnMc0620

ASKER

It's listed at the top as Dim rst As Recordset... not DAO.Recordset. But, that worked fine with the original. Here is the entire Sub:

Public Sub ShowWeekAppts(vWeekStart As Date)

'Copies the appointments info from tblAppointments into tblWeekData for the selected 7 day week
'Entry  (vWeekStart) = First day of the week (Sunday) to be displayed
'Exit   tblWeekData holds the appointments for the selected week

Dim rst As Recordset
Dim vDate As Date, vFirstDate As Date, vDateStop As Date
Dim vRow As Long, vCol As Long, vTemp As Long
Dim vArray(7 - 1, (1440 \ conPeriod) - 1) As Variant                                        '7 columns (days) x 48 (time slots) rows

    On Error GoTo ErrorCode

'Calc week numbers and dates from starting date and copy to table
    vFirstDate = vWeekStart                                                                 'set first date of week (Sunday)
    vDate = vFirstDate                                                                      'vDate = first day of week

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAppointments WHERE " _
    & "DateValue(ApptDate) <= #" & Format(vFirstDate + 6, "yyyy/m/d") & "# AND " _
    & "DateValue(ApptDate) >= #" & Format(vFirstDate, "yyyy/m/d") & "# ORDER BY ApptDate, ApptStartTime") 'fetch all appts for selected week

    Do Until rst.EOF
        'Calc start and end dates for current appointment time and save
        If DateValue(rst!ApptDate & rst!ApptStartTime) < vFirstDate Then                                        'if appt starts before current week then
            vDate = DateValue(vFirstDate) & " 00:00:01"                                     'start at first date/time in calendar
        Else                                                                                'else
            vDate = rst!ApptDate & rst!ApptStartTime                                        'start at first date/time of appt
        End If

        If DateValue(rst!ApptDate & rst!ApptEndTime) > vFirstDate + 6 Then                                 'if appt ends after end of current week then
            vDateStop = DateValue(vFirstDate + 6 + 1) & " 00:00:01"                         'stop at last day of week in calendar
        Else                                                                                'else
            vDateStop = rst!ApptDate & rst!ApptEndTime                                                     'stop at last date of appt
        End If

        'Now copy appt info into each row in array for current col
        Do                                                                                  'do--
            vCol = Weekday(vDate, conFirstDay) - 1                                          'calc column number for current Date
            vRow = (((Hour(vDate) * 60) + Minute(vDate)) \ conPeriod)                       'calc row number for curent date
            If conDayDitto = 0 Then                                                         'if conDayDitto = 0 then show appt in all time slots
                vArray(vCol, vRow) = vArray(vCol, vRow) & rst!Appt & "  "                   'add appt data to array
            Else                                                                            'if conDayDitto = 1 then show appt in first time slot and " in subsequent time slots
                If rst!AppointmentID <> vTemp Or vRow = 0 Then                              'if temp store different than data or start of new day then
                    vArray(vCol, vRow) = vArray(vCol, vRow) & rst!Appt & "  "               'add appt data to array
                    vTemp = rst!AppointmentID                                               'update temp store
                Else                                                                        'else
                    vArray(vCol, vRow) = "                ''"                               'show " char
                End If                                                                      'end if
            End If
            vDate = DateAdd("n", conPeriod, vDate)                                          'inc time ref by 30 mins
        Loop Until DateDiff("n", vDate, vDateStop) <= 0                                     'until all appts have been processed
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing

'Now copy contents of all rows in temp array into table (tblWeekData)
    For vRow = 0 To (1440 \ conPeriod) - 1                                                  'for each row in array
        CurrentDb.Execute "UPDATE tblWeekData SET " _
        & "Day1Data = " & QUOTE & RTrim(vArray(0, vRow)) & QUOTE & ", " _
        & "Day2Data = " & QUOTE & RTrim(vArray(1, vRow)) & QUOTE & ", " _
        & "Day3Data = " & QUOTE & RTrim(vArray(2, vRow)) & QUOTE & ", " _
        & "Day4Data = " & QUOTE & RTrim(vArray(3, vRow)) & QUOTE & ", " _
        & "Day5Data = " & QUOTE & RTrim(vArray(4, vRow)) & QUOTE & ", " _
        & "Day6Data = " & QUOTE & RTrim(vArray(5, vRow)) & QUOTE & ", " _
        & "Day7Data = " & QUOTE & RTrim(vArray(6, vRow)) & QUOTE & " " _
        & "WHERE RowNo = " & vRow + 1                                                       'copy to equiv record in table
    Next
    Exit Sub

ErrorCode:
    Beep
    MsgBox Err.Description

End Sub

Open in new window

Also, for noted purposes, Option Explicit lists

Public Const QUOTE = """"               'Used in SQL strings where the ' char may be used in text strings

Open in new window

Here's the exact error... very detailed!

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JohnMc0620

ASKER

OK, change the rst to DAO.Recordset, but still got error. Ticked out the error line like you said and got the following:

and, it highlighted this line:

If DateValue(rst!ApptDate & rst!ApptStartTime) < vFirstDate Then

Open in new window

I realized that I only needed the date value here anyway, so I removed the rst!ApptStartTime portion and re-ran code. Unfortunately, it errored out again with the very same error code on this line:

vDate = rst!ApptDate & rst!ApptStartTime

Open in new window

And, I finally realized what the problem is... There is no space added between the date and time!

The value has to be: vDate = rst!ApptDate & " " & rst!ApptStartTime

I also wrapped the rst!ApptDate with a DateValue() just to make sure no entries slipped by with time included.

mbizup, I am accepting your comment above as the solution because I did not know that error line was masking the debugger. Without knowing that, I may never have been able to solve this syntax issue! Thank you!!

JohnMc0620

ASKER

The solution is actually a syntax issue in connecting the date and time fields of the recordset. But, without the suggestion to remove the error handling line so the debugger would highlight the faulty code, I may never have figured this out! Two thumbs up to mbizup for the suggestion that got me to this solution!!

mbizup

Glad to help out!

Just remember to add that line back in when you are done :)