CODEWITHSUNDEEP
datems-access
Naresh B
Naresh B

Reputation: 1

An access query comparing the time part in a date field with values input in a form

I have an access query with predicates (conditions) on a date/time column called start_time. The condition is on two form fields defined with "Format" as "short time". The problem is that this query does not give correct results.

SELECT event_cust.*
FROM event_cust
WHERE  Format([start_time],"hh:mm") 
BETWEEN [Forms]![CustEventRptForm]![FromHour] 
AND [Forms]![CustEventRptForm]![ToHour]

Also tried it using Format([start_time], "short time") BETWEEN ... - did not work either.

Do we need anything in addition to above code to get the correct results?

I have tested with literal values as shown below and I get correct results with that.

SELECT event_cust.*
FROM event_cust
WHERE  Format([start_time],"hh:mm") BETWEEN '10:00' AND '13:00'

Upvotes: 0

Views: 112

Answers (2)

SunKnight0
SunKnight0

Reputation: 3351

Don't use string comparisons for date/times.

SELECT event_cust.*
FROM event_cust
WHERE  TimeValue([start_time]) >= CDate([Forms]![CustEventRptForm]![FromHour]) 
AND TimeValue([start_time]) <= CDate([Forms]![CustEventRptForm]![ToHour])

Upvotes: 0

JJ32
JJ32

Reputation: 1034

My guess is it's not interpreting the value from the form correctly.

Try adding quotes before and after the values:

SELECT event_cust.* 
FROM event_cust 
WHERE Format([start_time],"hh:mm") Between "" & [Forms]![CustEventRptForm]![FromHour] & "" 
   And "" & [Forms]![CustEventRptForm]![ToHour] & ""

Upvotes: 1

Related Questions