Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Question Unanswered: Expression for Query to Sort by Todays Date

    I have a Access query that needs to generate a report with only the records generated on the same day. For example, I generated 10 records in my database today, which is in addition to the prior records from the past few days. I would like my report to only show the records I made today, then tomorrow, etc...

    Sorry if confusing.

    Thanks... Keith

  2. #2
    Join Date
    Aug 2004
    Location
    Steamboat Springs, CO
    Posts
    37
    DateSerial(Year(Now()),Month(Now()),Day(Now())) will give you today's date in the form m/d/y

    Date() will give you the same result

    J
    Last edited by meremyb; 09-23-04 at 16:41.

  3. #3
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    use following as your criteria in your query;
    DateDiff("d",Date(),[yourdatefieldhere])=0

    it will check day difference of today's date and yourfield's date and show only the results returns 0.
    ghozy.

  4. #4
    Join Date
    Oct 2003
    Posts
    706
    Actually, the easiest way to do a "report for today" is to base the report on a query that includes the criteria "= Date()" for the appropriate field. The query returns only today's results.

    It's a little more complicated if you're working on a Timestamp type field (date and time), but not a lot.

    If you want the report to summarize several days, by date, then you need a field in the report-query that returns the day. Then group your report output by that field. A separate group of rows will be produced for each.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    yes, actually sundialsvcs is right, you can use =Date() as your criteria if your date field holds only date part without time part.
    ghozy.

  6. #6
    Join Date
    Sep 2004
    Posts
    5

    Question

    Thanks to all for the great support...

    Clarification (oops) - my date field also includes Time, so the table is formatted for Date/Time (and is displayed 9/24/2004 12:10:22 PM),

    how to expand the expression to include the time (or exclude to query only the date?

    thanks... Keith

  7. #7
    Join Date
    Sep 2004
    Posts
    161
    for exclude the time :
    SELECT Table1.*, Format([champ2],"dd/mm/yyyy") AS expr1
    FROM Table1
    WHERE (((Format([champ2],'dd/mm/yyyy'))=Date())) OR (((Format([champ2],'dd/mm/yyyy'))=Date()));
    Format(xxxx,'dd/mm/yyyy') depend of your régional setting and in which format you stored the date in your data base

Posting Permissions

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