Using Mod with Datediff (2000)

[Eggy]

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

[HansV]

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.

[Eggy]

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).

[HansV]

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?

[Eggy]

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.

[Eggy]

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

[HansV]

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.

[Eggy]

Thanks greatly Hans. I'll do that in future

Paul

[Eggy]

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

[HansV]

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

[HansV]

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.

[Eggy]

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]