Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Fun with date formats in queries

    Hello,

    I am trying to deduct time from a field which is a combination of two date fields. Here is what I want to do but I guess formatting causes it to get all screwed up:

    2 date fields in table:
    CreatedOn - Formatted as (Date/Time)
    CreatedTime - Formatted as (Date/Time)

    New query, contains the above two fields plus:
    DatTime= Format(([CreatedOn] & " " & [CreatedTime]),"mm/dd/yyyy hh:nn:ss am/pm")
    DedHour = #07:00:00 AM#

    The above query gives me notmal looking values. However, when I start an above query and do this:

    DatTime2= [DatTime]-[DedHour]

    It gives me an error.





    I got something to work, I just hate it because it involves update queries and I hate those, but the concept is exactly the same as above. How can I get the above to return a correct date/time?

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Lookup the DateDiff function in the Help file.

  3. #3
    Join Date
    Oct 2009
    Posts
    204
    Yeah, I tried playing with datediff a couple different ways, but to no avail. I guess I can't figure out how to get it to return in general date format, which is what I am looking for. Whenever I try to format my result as general date, it looks way off.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You have to keep a couple of things in mind when working with dates. The 'date' and the format of the date value are completely separate. The date is stored as a number. It is the number of days from a given date.

    The format makes that number human readable. When you use Format to change the way it looks you are actually converting the date to a string. Doing arithmetic with strings is going to give you some weird results.

    DateDiff is not the function you are looking for. DateAdd is the function that allows you to add or subtract minutes, days, etc from a given date.

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Yes, DateAdd was it. Thanks 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
  •