Use Time value in SQL VBA as criteria

[Amras]

Good afternoon, all. I'm trying to create an SQL query in VBA that will return a rep's schedule preferences by assembling the SELECT, FROM and WHERE portions of the query based on the report user's input on a form. The report user will check any of seven check-boxes to indicate the requested day/days of the week, then two text boxes into which they'll enter a start time and an end time. The data is stored in the table as Medium Time, however when the assembled SQL is tested, there are no results as the data in the table has no date component so its defaulting to 1/1/1900 as it's search criteria. With DatePart you can query for Hour or Minute, but not both (perhaps the rep has requested a schedule that starts at 8:30 am rather than 8:00 am or 9:00 am). This is the code that I have so far (I've only included the final checkbox as all of the other checkboxes mirror it):

    If chkSaturday Then
        strSQLSaturday = "tblSchedulePreference.RequestSaturdayStart, tblSchedulePreference.RequestSaturdayEnd, "
        strWhereSaturday = "((tblSchedulePreference.RequestSaturdayStart)= # " & [Forms]![frmSchedulePreferenceReports]![txtBeginTime] & " #) AND ((tblSchedulePreference.RequestSaturdayEnd)=#" & [Forms]![frmSchedulePreferenceReports]![txtEndTime] & " #) AND "
    Else
        strSQLSaturday = ""
        strWhereSaturday = ""
    End If
    
    strSQLSelect = "SELECT tblSchedulePreference.LanID, tblEmployees.FullName, " & strSQLSunday & strSQLMonday & strSQLTuesday & strSQLWednesday & strSQLThursday & strSQLFriday & strSQLSaturday
    strSQLSelect = Left(strSQLSelect, Len(strSQLSelect) - 2)
    strSQLSelect = strSQLSelect & " "

    strSQLFrom = "FROM tblSchedulePreference INNER JOIN tblEmployees ON tblSchedulePreference.LanID = tblEmployees.LanID "

    strSQLWhere = strWhereSunday & strWhereMonday & strWhereTuesday & strWhereWednesday & strWhereThursday & strWhereFriday & strWhereSaturday
    strSQLWhere = Left(strSQLWhere, Len(strSQLWhere) - 5)
    strSQLWhere = "Where (" & strSQLWhere & ");"

    strSQL = strSQLSelect & strSQLFrom & strSQLWhere

I apologize if I'm making a obvious mistake. Any help you can provide will be appreciated!

[ItsMe]

In Access, Date/Time is stored as a number. I believe it fits into a Double. Formatting does not have anything to do with how the number is stored. Formatting will determine how the number is displayed to humans.

[Micron]

Thinking you could concatenate DatePart values, or you could use the TimeSerial function.
Just guessing since I can't tell what your doing with the time part when you query.
FYI: the integer portion of the double precision date number is the date; the decimal portion is the time.

[CJ_London]

and to follow on from Microns explanation, the date is expressed as the number of days from 'day zero' - today 2nd September is 42615, yesterday was 42614.

And time is expressed as a fraction calculated as the number of seconds in the day to 'time' divided by the number of seconds in the day - e.g. time is now is 09:08:25 - number of seconds =(9hrs*60*60)+(8*60)+25=32905. number seconds in the day 24*60*60=86400 so expressed as 32905/86400=0.380844907407407

So now() will return a value of 42615.380844907407407

[ItsMe]

...
So now() will return a value of 42615.380844907407407

Apparently, you are living in the past. I get a later time.

[CJ_London]

I'm always trying to catch up