Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    81

    Question Unanswered: Convert now() to date()

    I have a whole mass of records with a date format of dd/mm/yy hh/mm/ss and I want to remove the hh/mm/ss component since it's messing with my grouping!

    How can I do this easily without manually changing every single record?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    in your query, add a field like so:
    MyGroupDate:Format([YourDate],"yyyymmdd")

    This creates a text field from the date in the format yyyymmdd (for example, today, June 26, would be 20040628). In this format, the sort and grouping will work correctly.

    In your report, Group by this field, but display the date field (so that it looks right to the end users).

    Have fun

  3. #3
    Join Date
    Oct 2003
    Posts
    81
    Yep, tried that one. The problem there is that it converts it to that text string. Then when you want to do sorting and grouping on the report, it only allows "Each Value" or "Prefix Character" and not the day, week etc.

    The day, week are crucial to my report.

    Any other ideas? Is there an update query I can run which will delete that time section only from the date field?

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Have you tried =Date([YourDate])?

    =Date(Now()) will return only the date portion.

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can use the CDate function to change the text to Date. So that you can sort your data according to Date and not to text.

  6. #6
    Join Date
    Oct 2003
    Posts
    81

    Talking

    Cool, thanks guys. That worked nicely.

    CHEERS!

Posting Permissions

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