Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    13

    Unanswered: Groups Dates by Hours

    Hi,

    I am trying to do a stats query that will group all of the date fields in "IncidentDate" which is a date/time field by the hour itself.

    e.g.

    12:00-01:00 - 5
    01:00-02:00 - 7
    02:00-03:00 - 3
    ...
    23:00-24:00 - 4

    Through an entire 24 hour period.

    Any help would be appreciated...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the group by
    eg
    group by <mydatecolumn>,hour(<mydatecolumn>)
    to derive the day of the week use the weekday weekday
    you could write a function to retrieve the day of the week - might be better to put it in the report rather than the sql for performance reasons. In the report place a function that converts the integer value returned by weekday back into a string, alternatively format(<mydatecolumn>,"ddd")
    Last edited by healdem; 02-01-06 at 13:41.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    hour(incidentdate) .... at a guess

    Chris

  4. #4
    Join Date
    Feb 2004
    Posts
    13
    How simple was that, thanks guys...

    SELECT Hour([IncidentDate]) AS Hours, Count(Accident.IncidentDate) AS CountOfIncidentDate
    FROM Accident
    GROUP BY Hour([IncidentDate])
    ORDER BY Hour([IncidentDate]);

    The issue now is that I have to convert the hour to a more readable format as opposed to "20" for 8:00.
    When I put the IIF stmt in the control source in the properties window it was too complex so I have to use VBA to do that.

    Now how do I capture the value of the txtBox on the report to say if its then then write then.
    If txtHours = "2" Then .. Blah.. Blah

    Is it something like

    If Reports.txtHours....

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do your formatting in the report / form, leave the query to do the data handling and processing.

    the two tasks are completely diffferent, its best to use the appropriate tool for the task
    SQl for data manipulation ad extraction - ie the Q:Query in SQL

    and your user interface for formatting and presentation

    sometimes you need to do soem internmediate processing in SQL but if you can avoid it then do so..
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Posts
    13
    I know that I have to do this work on the report in VBA but I don't know how to grab the value from the TXT BOX and place it into VBA to check and then change the value.

    Is it something like:

    If me.txtHours "2" Then
    me.txtHours = "2:00"
    End if

    ?

Posting Permissions

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