Help with DateDiff function 
Author Message
 Help with DateDiff function

Is big but plese try to help me :

I have one database with this structure and one big
trouble:

Field Name      -       Data Type

Employee Code           Text
Employee Name           Text
Date                    Date/Time / Format : Short Time
In                      Date/Time / Format : Short Time
Lunch Out               Date/Time / Format : Short Time
Lunch In                Date/Time / Format : Short Time
Out                     Date/Time / Format : Short Time
Day Total               Date/Time (Expr1: Sum([LunchOut]!
[Lunch Out]-[Check In]![In]+[Check Out]![Out]-[Lunch In]!
[Lunch In]) / Format : Short Time

REPLAY :

The problem is in how Access stores date/time values. A
Date/Time is really designed for *a point in time* - not a
duration. It's stored in the database as a Double Float
number, a count of days and fractions of a day since
midnight, December 30, 1899 - so if you subtract two
date/times you'll get days and fractions of a day; if you
add these up and the sum comes to over 24 hours, it'll be
a time of day on *December 31, 1899* - as you can see if
you format the sum to Long Date! Instead, I'd suggest
using the DateDiff() function to calculate an integer
number of *minutes* - signified by n for miNutes, since m
means Months - worked. Your Day Total expression could be
DateDiff("n", [In], [Out]) - DateDiff("n", [Lunch out],
[Lunch In]) You can then sum this across records using a
totals query. To display the result as hours:minutes, you
can use an expression like ShowTotal: SumOfTime \ 60 &
Format(SumOfTime MOD 60, ":00") where SumOfTime is the
total from your query.

I did this suggestion and my Day Total change to :

Expr1: DateDiff("n",[in],[out])-DateDiff("n",[lunch out],
[lunch in])

Work but in one sum like that :

In : 9:20
Lunch Out : 12:30
Lunch In : 13:05
Out : 18:00

The real total is 483 but this Express give one total 485,
another sample

In : 9:25
Lunch Out : 12:36
Lunch In : 12:50
Out : 17:59

The real total is 492 but this Express give one total 500.

PLEASE ANY HELP

Fernando Salgueiro



Sun, 18 Sep 2005 02:11:05 GMT  
 Help with DateDiff function
Already answered in microsoft.public.access, where you posted the same
question.

If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)

If you're using Microsoft's web interface to post, please note that you can
type the names of the various groups into the Newsgroup box. Separate each
newsgroup name with a semicolon.

See http://www.mvps.org/access/netiquette.htm for more information.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


Quote:
> Is big but plese try to help me :

> I have one database with this structure and one big
> trouble:

> Field Name - Data Type

> Employee Code Text
> Employee Name Text
> Date Date/Time / Format : Short Time
> In Date/Time / Format : Short Time
> Lunch Out Date/Time / Format : Short Time
> Lunch In Date/Time / Format : Short Time
> Out Date/Time / Format : Short Time
> Day Total         Date/Time (Expr1: Sum([LunchOut]!
> [Lunch Out]-[Check In]![In]+[Check Out]![Out]-[Lunch In]!
> [Lunch In]) / Format : Short Time

> REPLAY :

> The problem is in how Access stores date/time values. A
> Date/Time is really designed for *a point in time* - not a
> duration. It's stored in the database as a Double Float
> number, a count of days and fractions of a day since
> midnight, December 30, 1899 - so if you subtract two
> date/times you'll get days and fractions of a day; if you
> add these up and the sum comes to over 24 hours, it'll be
> a time of day on *December 31, 1899* - as you can see if
> you format the sum to Long Date! Instead, I'd suggest
> using the DateDiff() function to calculate an integer
> number of *minutes* - signified by n for miNutes, since m
> means Months - worked. Your Day Total expression could be
> DateDiff("n", [In], [Out]) - DateDiff("n", [Lunch out],
> [Lunch In]) You can then sum this across records using a
> totals query. To display the result as hours:minutes, you
> can use an expression like ShowTotal: SumOfTime \ 60 &
> Format(SumOfTime MOD 60, ":00") where SumOfTime is the
> total from your query.

> I did this suggestion and my Day Total change to :

> Expr1: DateDiff("n",[in],[out])-DateDiff("n",[lunch out],
> [lunch in])

> Work but in one sum like that :

> In : 9:20
> Lunch Out : 12:30
> Lunch In : 13:05
> Out : 18:00

> The real total is 483 but this Express give one total 485,
> another sample

> In : 9:25
> Lunch Out : 12:36
> Lunch In : 12:50
> Out : 17:59

> The real total is 492 but this Express give one total 500.

> PLEASE ANY HELP

> Fernando Salgueiro



Sun, 18 Sep 2005 06:53:42 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Help with the math in a datediff function

2. DateDiff and other functions HELP

3. Datediff function, help needed

4. Function DateDiff results as condition

5. Excluding weekends from DateDiff Function

6. DateDiff-function

7. DateDiff in function

8. DateDiff Function

9. Negative values from DateDiff function

10. datediff function

11. Calculating Time Difference using DateDiff Function in VBScript

12. DateDiff function

 

 
Powered by phpBB® Forum Software