How to query for a time range.

Question

I need to build a query that determines whether a transaction occured between 6:30 AM and 5:00 PM. I split out the time from the transaction date/time so just the time is in the field.

I am looking for the best operator/function and the proper syntax to use. I tried Between, Hour, Minute functions but get errors.

I think the big issue is to get Access to recognize the field as time and not as a string or integer.

I tried this "IIf(TimeValue([Transaction Date/Time]) Between #6:30:00 AM# And #5:00:00PM#,"False","True")", but I get a syntax error. The cursor is on 'AM' in the query string.

I am using Access 2013.

Answer 1

I'm just guessing here but you don't have a space between the time and "PM". And if you are looking for that time range you have your IIf return parameter backwards.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

You don't need to call the IIF function.  Just format the return value of the BETWEEN….AND operation:

FORMAT(TIMEVALUE([Transaction Date/Time]) BETWEEN #6:30:00 AM# AND #5:00:00 PM#,"True/False")

---

Ken Sheridan, Stafford, England

Answer 3

I tried it and I am still getting a syntax error.

Format(TimeValue([Transaction Date/Time]) Between #6:30:00 AM# And #5:00:00 PM#,"True/False")

Answer 4

Hi, TWIKLE

You can use query like below

>> SELECT * from stud where ((TimeValue(time) Between #6:30:00 AM# And #5:00:00 PM#));

>>

SELECT * from stud where Format(TimeValue(time) Between #6:30:00 AM# And #5:00:00 PM#,"True/False");

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 5

This works:

Sub Test()
  Dim A As Date
  Dim B As Boolean
  
  A = #5:00:00 AM#
  B = (A > #4:30:00 AM# And A < #5:00:00 PM#)
  Debug.Print B
End Sub

---

Best regards, George

Answer 6

1.  Are you absolutely sure that the Transaction Date/Time column is of DateTime data type?

2.  Are you applying a criterion to the computed column?  If so, whether you call the IIF function in the way you described, or the FORMAT function, the returned value will in each case be a string expression, so the criterion would need to test for such, i.e. "True", not the Boolean constant TRUE.

What you are attempting here should be a trivial matter, so there is clearly something which is not apparent from the information you've given us.  Post the full SQL statement of the query; that might help the dog see the rabbit.

---

Ken Sheridan, Stafford, England

Answer 7

It appears to be working. Here is the SQL statement. Notice the date:12/30/1899. I didn't put that there, Access did.  Also notice the time values are different from my query.

SQL:

SELECT tblComdata.[Transaction Date/Time], DateValue([Transaction Date/Time]) AS [Date], TimeValue([Transaction Date/Time]) AS [Time], Format(TimeValue([Transaction Date/Time]) Between #12/30/1899 6:30:0# And #12/30/1899 17:0:0#,"True/False") AS [Business Hours]
FROM tblComdata;

Query:

Business Hours: Format(TimeValue([Transaction Date/Time]) Between #6:30:00 AM# And #5:00:00 PM#,"True/False")

Answer 8

The strange date/time formats are merely a result of Access's implementation of the DateTime data type.  In Access there is no such thing as a date value or a time value, only a date/time value.  A date/time value is implemented as a 64 bit floating point number in which the integer part represents the days, the fractional part the time of the day.  The stored value is simply a duration in days since 30 December 1899 00:00:00, which is 'time-zero' in the implementation.

When you enter a date without a time you are in reality entering an integer number; when you enter a time without a date you are entering a fractional value with an integer value of zero. The former represents midnight at the start of the day in question, the latter represents the time of day on 30 December 1899, which is why the values appear as such in the SQL statement.  In effect, however, they work as you intend.

---

Ken Sheridan, Stafford, England