Total in a report

[Guest]

I have a report that has the following fields: Volunteer Name, Time In, Time
Out, Total Hours(formatted hh\nn), Dollar Value(which is calculated at 6.15
for Total Hours). I also created a query with a Total Row and have an
expression Hours:Sum([Time Out] - [Time In]). I need to multiply the Total
Hours by $6.15. When I do this on the report it's giving the
following...5.45 hours multiplied by $6.15 is giving me $1.47....I am very
new at this and would appreciate any help that you can extend me.

Doris

 

[Douglas J. Steele]

Try:

Hours:Sum(DateDiff("n", [Time Out],[Time In]))/60

Date/Times are stored as 8 byte floating point numbers, where the integer
portion represents the date as the number of days relative to 30 Dec, 1899,
and the decimal portion represents the time as a fraction of a day. That
means they're not really suited for storing durations: as soon as the
duration exceeds 24 hours, you're not going to see that portion in excess of
24 hours when you format as hh\nn. A time of 5:45 is going to be stored as
0.239583333333333, which is what's leading to the results you're seeing. You
could simply multiple by 24 (0.239583333333333 * 24 = 5.75, or 5 and 3/4
hours), but you're better off not misusing the data type.

 

[Guest]

Do I place that formula in my query on an unbound text box? I am assuming
it's place in the query....when I did I got a negative number example
-3.00...when I calculate the $6.15 for the total hours do I do it on the form
in an unbound text box? Thanks ahead of time for your help...I do definitely
appreciate it.

Doris

Try:

Hours:Sum(DateDiff("n", [Time Out],[Time In]))/60

Date/Times are stored as 8 byte floating point numbers, where the integer
portion represents the date as the number of days relative to 30 Dec, 1899,
and the decimal portion represents the time as a fraction of a day. That
means they're not really suited for storing durations: as soon as the
duration exceeds 24 hours, you're not going to see that portion in excess of
24 hours when you format as hh\nn. A time of 5:45 is going to be stored as
0.239583333333333, which is what's leading to the results you're seeing. You
could simply multiple by 24 (0.239583333333333 * 24 = 5.75, or 5 and 3/4
hours), but you're better off not misusing the data type.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a report that has the following fields: Volunteer Name, Time In,
Time
Out, Total Hours(formatted hh\nn), Dollar Value(which is calculated at
6.15
for Total Hours). I also created a query with a Total Row and have an
expression Hours:Sum([Time Out] - [Time In]). I need to multiply the
Total
Hours by $6.15. When I do this on the report it's giving the
following...5.45 hours multiplied by $6.15 is giving me $1.47....I am very
new at this and would appreciate any help that you can extend me.

Doris

 

[Douglas J. Steele]

That was intended to replace what you had shown in your original post.
Unfortunately, I mistyped: as you've discovered, [Time Out] and [Time In]
need to be reversed from what I suggested. Based on what you said, too, it
looks as though you're getting Integer arithmetic, so try:

Hours:CSng(Sum(DateDiff("n", [Time In],[Time Out])))/60

or

Hours:Sum(DateDiff("n", [Time In],[Time Out]))/60!



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Do I place that formula in my query on an unbound text box? I am assuming
it's place in the query....when I did I got a negative number example
-3.00...when I calculate the $6.15 for the total hours do I do it on the
form
in an unbound text box? Thanks ahead of time for your help...I do
definitely
appreciate it.

Doris

Try:

Hours:Sum(DateDiff("n", [Time Out],[Time In]))/60

Date/Times are stored as 8 byte floating point numbers, where the integer
portion represents the date as the number of days relative to 30 Dec,
1899,
and the decimal portion represents the time as a fraction of a day. That
means they're not really suited for storing durations: as soon as the
duration exceeds 24 hours, you're not going to see that portion in excess
of
24 hours when you format as hh\nn. A time of 5:45 is going to be stored
as
0.239583333333333, which is what's leading to the results you're seeing.
You
could simply multiple by 24 (0.239583333333333 * 24 = 5.75, or 5 and 3/4
hours), but you're better off not misusing the data type.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a report that has the following fields: Volunteer Name, Time In,
Time
Out, Total Hours(formatted hh\nn), Dollar Value(which is calculated at
6.15
for Total Hours). I also created a query with a Total Row and have an
expression Hours:Sum([Time Out] - [Time In]). I need to multiply the
Total
Hours by $6.15. When I do this on the report it's giving the
following...5.45 hours multiplied by $6.15 is giving me $1.47....I am
very
new at this and would appreciate any help that you can extend me.

Doris

 

[Guest]

Thanks Doug, the Hours:Sum(DateDiff("n", [Time In],[Time Out]))/60 worked
out real well....Doris

That was intended to replace what you had shown in your original post.
Unfortunately, I mistyped: as you've discovered, [Time Out] and [Time In]
need to be reversed from what I suggested. Based on what you said, too, it
looks as though you're getting Integer arithmetic, so try:

Hours:CSng(Sum(DateDiff("n", [Time In],[Time Out])))/60

or

Hours:Sum(DateDiff("n", [Time In],[Time Out]))/60!



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Do I place that formula in my query on an unbound text box? I am assuming
it's place in the query....when I did I got a negative number example
-3.00...when I calculate the $6.15 for the total hours do I do it on the
form
in an unbound text box? Thanks ahead of time for your help...I do
definitely
appreciate it.

Doris

Try:

Hours:Sum(DateDiff("n", [Time Out],[Time In]))/60

Date/Times are stored as 8 byte floating point numbers, where the integer
portion represents the date as the number of days relative to 30 Dec,
1899,
and the decimal portion represents the time as a fraction of a day. That
means they're not really suited for storing durations: as soon as the
duration exceeds 24 hours, you're not going to see that portion in excess
of
24 hours when you format as hh\nn. A time of 5:45 is going to be stored
as
0.239583333333333, which is what's leading to the results you're seeing.
You
could simply multiple by 24 (0.239583333333333 * 24 = 5.75, or 5 and 3/4
hours), but you're better off not misusing the data type.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a report that has the following fields: Volunteer Name, Time In,
Time
Out, Total Hours(formatted hh\nn), Dollar Value(which is calculated at
6.15
for Total Hours). I also created a query with a Total Row and have an
expression Hours:Sum([Time Out] - [Time In]). I need to multiply the
Total
Hours by $6.15. When I do this on the report it's giving the
following...5.45 hours multiplied by $6.15 is giving me $1.47....I am
very
new at this and would appreciate any help that you can extend me.

Doris