Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008
    Posts
    2

    Unanswered: Querying timedate field, variable dates, static times

    I'm having trouble building what I thought should have been a simple report in MS Access, and was hoping for some guidance.

    I need to count the number of trouble tickets a customer support rep updated during a specific hour. I need to be able to specify a date. The time is static, as the hour of interest does not change (ie, how many tickets did John update between noon and 1pm on a given day).

    The data is being pulled from an MS SQL database generated by our ticketing system. Time and date information is stored in a single field of timedate type. This has been the first of my hurdles.

    This query gives me exactly what I want, one day at a time:

    --------------------------------
    SELECT COUNT(*) AS John

    FROM
    (SELECT DISTINCT INCIDENT_NUMBER
    FROM dbo_HISTORY_AUDIT
    INNER JOIN Reps ON Reps.GWI_ID = dbo_HISTORY_AUDIT.ID_OWNER

    WHERE
    (Reps.Name = "John")
    AND
    (DT_CREATED BETWEEN #6/9/2010 10:55:00 AM# AND #6/9/2010 12:10:00 PM#))
    --------------------------------

    My problem is that I can't figure out how to allow the user to input a date or date range (select from a calendar form, manual input, whatever) but search on the static time. How do I separate the time and date in my query when the source data has them stored in one field?

    Thanks for any assistance!

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    before getting to the Report; it would be best to get the query working right...

    have you set up a query using plain old query design grid? put in dates as criteria??

    try using a wild card * for the time in addition to the date...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •