Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Mod with Datediff (2000)

    I'm actually trying to do a calculation to display the time difference (in days, hours & minutes) between the date-times in two fields. I can get results in any one of these, but I'd rather it were as I stated. That's why I figured using 'mod' would help. Here's something quick & nasty I've started with & then got stuck;

    SELECT Query1.[Lot-No], Query1.[Date-timeOn], Query1.[Date-timeOff], DateDiff("h",[Date-timeOn],[Date-timeOff]) AS TimeTaken, DateDiff("n",[Date-timeOn],[Date-timeOff]) Mod "h" AS TimeTakenM
    FROM Query1;


    TIA
    Paul

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Mod with Datediff (2000)

    Welcome to Woody's Lounge!

    The Mod function only works with numbers, not with strings such as "h". Why don't you just create a calculated column

    TimeTaken: [Date-timeOff]-[Date-timeOn]

    (in SQL: [Date-timeOff]-[Date-timeOn] AS TimeTaken)

    and set its Format property to General Date or a custom format that includes the date and time.

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Mod with Datediff (2000)

    I used this;

    [Date-timeOff]-[Date-TimeOn] AS TimeTakenV2

    & got the #Error in the resulting datasheet view. So I tried it with grouping on & received a "Data type mismatch in criteria expression" dialogue box (oh & had the format set to dd:hh:nn).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Mod with Datediff (2000)

    I assumed that Date-timeOn and Date-timeOff were fields of type date/time and that Date-timeOff would be later than Date-timeOn. Is that correct?

  5. #5
    New Lounger
    Join Date
    Dec 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Mod with Datediff (2000)

    Both of those fields are "General Date" format, though it is probably worth mentioning that they're created from concatenated date fields & time fields (done this way, due to incompetent user). And yes, [Date-timeOff] is the later of the two fields.

  6. #6
    New Lounger
    Join Date
    Dec 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Mod with Datediff (2000)

    Ah, that's done it, well almost. To speed up data input, I've set the default value for the [DateOff] field to Now(), & used 'Left' to shorten it for the query. Clearly, this now make it awkward in the query. I can easily edit the current records to suit, but what would be the best way forward from here?

    I could leave the default value blank.
    If there is a way to set the default to Today(), that would be nice,
    or maybe an automated way of shortening the Now() to record only the date, & not the time as well.

    Any ideas?

    TIA
    Paul

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Mod with Datediff (2000)

    Set the default value to Date() instead of Now(). Now() is the current date + time, whereas Date() is just the current date. You'll still have to remove the time values in existing records.

  8. #8
    New Lounger
    Join Date
    Dec 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Mod with Datediff (2000)

    Thanks greatly Hans. I'll do that in future

    Paul

  9. #9
    New Lounger
    Join Date
    Dec 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Mod with Datediff (2000)

    Ah heck, thought it was all sorted & now I'm getting spurious results. There appears to be no logic to the number of hours displayed, though the minutes seems OK.

    I'm attaching a stripped down example for perusal.

    All help gratefully received [img]/forums/images/smilies/smile.gif[/img]

    Paul

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Mod with Datediff (2000)

    Edited by HansV to correct typo

    But if they are concatenated from a date and a time field with &, they are not date values but text. Try this, substituting the correct field names:

    ([DateOff]+[TimeOff])-([DateOn]+[TimeOn]) AS TimeTakenV2

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Mod with Datediff (2000)

    Please excuse me - there was a typo in the expression I suggested, one of the - signs should have been a +. I have corrected my reply. In your database, it should be:

    TimeTakenV2: ([Date]+[Time-Off])-([DateOn]+[Time-On])

    Note: there is one record (with Lot-No 04 43859 03 10) where Time-Off is before Time-On, resulting in an incorrectly displayed TimeTakenV2 value.

  12. #12
    New Lounger
    Join Date
    Dec 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Mod with Datediff (2000)

    Oops, I should have typed it in myself, then I'd have noticed that too. Never mind, your help has been, as ever, very welcome.
    Ta
    Paul

    PS Yeah, I know he'd put one in wrong, figures [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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