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!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 BrockCIOCommented:
Maybe your ApptDate contains Null values.

JohnMc0620Author Commented:
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!
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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")
JohnMc0620Author Commented:
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!
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?
JohnMc0620Author Commented:
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
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnMc0620Author Commented:
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!!
JohnMc0620Author Commented:
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!!
Glad to help out!

Just remember to add that line back in when you are done :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.