none
How to query for a time range. RRS feed

  • 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.

    Monday, April 11, 2016 8:26 PM

Answers

  • 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

    • Marked as answer by TWIKLE Tuesday, April 12, 2016 11:17 PM
    Tuesday, April 12, 2016 7:03 PM

All replies

  • 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


    Monday, April 11, 2016 9:36 PM
  • 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

    Monday, April 11, 2016 10:26 PM
  • 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")

    Monday, April 11, 2016 10:36 PM
  • 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.


    Tuesday, April 12, 2016 2:50 AM
    Moderator
  • 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

    Tuesday, April 12, 2016 5:13 AM
  • 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

    Tuesday, April 12, 2016 10:46 AM
  • 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")

    Tuesday, April 12, 2016 4:04 PM
  • 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

    • Marked as answer by TWIKLE Tuesday, April 12, 2016 11:17 PM
    Tuesday, April 12, 2016 7:03 PM