Between Function on SQL via VBA (1 Viewer)

chayong91

New member
Local time
Today, 14:33
Joined
Aug 24, 2016
Messages
6
Hi guys,

I need help on my code.
Code:
    Dim qry As DAO.QueryDef
    
    Set qry = CurrentDb.QueryDefs("qry_Report")
                       
    qry.SQL = "SELECT tbl_Sample.* " _
                & "FROM tbl_Sample " _
                & WHERE TIME Between #" & Me.cmb_HoursFrom.Value & "# and   #" & Me.cmb_HoursTo.Value & "# "
                
    DoCmd.OpenReport rpt_DailyWW, acViewPreview

cmb_HoursFrom/To contains 00:00-23:00. it run perfectly fine. but when my time is 01:00, 04:00, 07:00, 10:00, 13:00, 16:00, 19:00 and 22:00 it only displays the hour before it.

if i choose 00:00 to 04:00 it will only display 00:00 to 03:00.

i even try using >= and <= it still do the same. i even try to use the exact value not using the cmb_HoursFrom/To.

can you please help me.

thank you in advance.

ps. i apologize in advance, i'm still new to access.
 
Last edited:

sneuberg

AWF VIP
Local time
Today, 04:33
Joined
Oct 17, 2014
Messages
3,506
This web page says

When you compare time values, you may receive inconsistent results because a time value is stored as the fractional part of a double-precision, floating-point number

I'd be curious to know what happens if you add a second to the Me.cmb_HoursTo.Value so that it would be 04:00:01.

Anyway I'd try using the format function to convert the times to strings. That would at least get rid of round off problems and in this case since you are using 24 hour format I believe string comparison would still get you the right answers.
 

sneuberg

AWF VIP
Local time
Today, 04:33
Joined
Oct 17, 2014
Messages
3,506
Maybe something like:

qry.SQL = "SELECT tbl_Sample.* " _
& " FROM tbl_Sample " _
& " WHERE Format(TIME, ""Short Time"") Between '" & Format(Me.cmb_HoursFrom.Value, "Short Time") & "' and '" & Format(Me.cmb_HoursTo.Value, "Short Time") & "'"


Edit: Test database is attached.
 

Attachments

  • TimeBetweenTest.accdb
    432 KB · Views: 59
Last edited:

moke123

AWF VIP
Local time
Today, 07:33
Joined
Jan 11, 2013
Messages
3,849
as an aside, note that TIME is a reserved word.
 

Users who are viewing this thread

Top Bottom