SQL Query Syntax in Access VBA Code (Type Mismatch)

Posted on 2013-09-20
Medium Priority
Last Modified: 2013-09-20
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!
Question by:JohnMc0620
  • 5
  • 5
LVL 61

Expert Comment

ID: 39509587
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

LVL 53

Expert Comment

by:Gustav Brock
ID: 39509612
Maybe your ApptDate contains Null values.


Author Comment

ID: 39509629
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!
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

LVL 61

Expert Comment

ID: 39509641
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")

Author Comment

ID: 39509700
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!
LVL 61

Expert Comment

ID: 39509732
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?

Author Comment

ID: 39509779
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
    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
    Exit Sub

    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!type mismatch error
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 39509803
Try changing it to explicitly be

Dim rst AS DAO.Recordset

The DAO can make a difference, depending on the order in which your references are listed.

If that doesn't help, temporarily tick out the ON Error line:

'On Error GoTo ErrorCode

That will give you a better idea of what is causing the error, by making the debugger highlight the problematic line.

Author Comment

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

runtime error 13
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!!

Author Closing Comment

ID: 39509873
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!!
LVL 61

Expert Comment

ID: 39509874
Glad to help out!

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question