Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Location
    California
    Posts
    8

    Unanswered: Last 24 hours report

    I have a database written in access 97.
    One of the field stored in the database is the date
    I would like to report the entries for the last 24 hours.
    What I did is I created a query and I sorted by decending date. This works because I see the new entries on top. However, the report shows all the rest of the entries.
    How can I customize the report to automatically (without typing date range) to show the data for the last 24 hours.
    Thank you.

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    You don't need to sort your records at all. you should add a calculated field to your query something like this:
    Difference: Datediff("h", yourdatefield, now())

    then enter this line as criteria for the calculated field: <24
    what you do here is creating a calculated field which calculates the time difference between your datefield and current time in hourly interval then checks if it is less than 24.
    ghozy.

  3. #3
    Join Date
    Sep 2002
    Location
    Finland
    Posts
    34
    Do not use calculated field with datediff -function, it could make full scan into to table. If you want see datediff-values, it's ok, but don't use it in criteria.

    Put in date field crieteria >DateAdd("h";-24;Now())

    Example:
    SELECT DatesTable.OwnDate
    FROM DatesTable
    WHERE (((DatesTable.OwnDate)>DateAdd("h",-24,Now())));

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I agree, seems like a better apporach.
    ghozy.

  5. #5
    Join Date
    Jun 2004
    Location
    California
    Posts
    8
    mmmmmm when I entered this command
    >DateAdd("h";-24;Now())
    in the criteria field under Start Date field, I can not save the query. I am using access 97.

    Here is what I have in the original query:
    ========================
    SELECT [Saved Maintenance Log].[Input Date], [Saved Maintenance Log].[Input Time], [Saved Maintenance Log].[Start Date], [Saved Maintenance Log].[Start Time], [Saved Maintenance Log].[Work Time], [Saved Maintenance Log].Mechanic1, [Saved Maintenance Log].Mechanic2, [Saved Maintenance Log].Mechanic3, [Saved Maintenance Log].Mechanic4, [Saved Maintenance Log].Group, [Saved Maintenance Log].Area, [Saved Maintenance Log].Line, [Saved Maintenance Log].Equipment, [Saved Maintenance Log].Type, [Saved Maintenance Log].Item, [Saved Maintenance Log].Task
    FROM [Saved Maintenance Log]
    ORDER BY [Saved Maintenance Log].[Start Date] DESC;

    ======================

  6. #6
    Join Date
    Sep 2002
    Location
    Finland
    Posts
    34
    Try copying this sql:
    SELECT [Saved Maintenance Log].[Input Date], [Saved Maintenance Log].[Input Time], [Saved Maintenance Log].[Start Date], [Saved Maintenance Log].[Start Time], [Saved Maintenance Log].[Work Time], [Saved Maintenance Log].Mechanic1, [Saved Maintenance Log].Mechanic2, [Saved Maintenance Log].Mechanic3, [Saved Maintenance Log].Mechanic4, [Saved Maintenance Log].Group, [Saved Maintenance Log].Area, [Saved Maintenance Log].Line, [Saved Maintenance Log].Equipment, [Saved Maintenance Log].Type, [Saved Maintenance Log].Item, [Saved Maintenance Log].Task
    FROM [Saved Maintenance Log]
    WHERE ((([Saved Maintenance Log].[Start Date])>DateAdd("h",-24,Now())));

    (I change only last row in sql-clause)

    What error message you get, when you try to save query?

  7. #7
    Join Date
    Jun 2004
    Location
    California
    Posts
    8
    Excellent. The SQL code you gave me works very well. Thank you for the help.

Posting Permissions

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