Time/Decimal convert

[neilmcmor]

I have a query with 2 fields I wish to multiply together to get a value for a feild, [Hours_Lost]. One is a short time field and one is a number field. The short time field is [Hours] and the number field is [people] I have been trying the following but no luck. Abnyone any ideas?

Hours_Lost: [People]*(CDbl([Hours])*24)

 

[KenHigg]

Hours_Lost: (DatePart("H", Me.Hours))*(me.[People])

???
ken

 

[neilmcmor]

I am pretty sure that will only multiply the hours and not the minutes. Lose a lot of money if I did that. 40 people with 45 mins each. What I have to do is multiply the whole time.

 

[KenHigg]

Hours_Lost: (DatePart("n", Me.Text4) / 60) + DatePart("H", Me.Text4)*(me.[People])

???
ken

 

[neilmcmor]

Had to place another set of brackets round Ken but thanks for your help as it works fine now.

Hours_Lost: ((DatePart("n", Hours) / 60) + DatePart("H", Hours))*(me.[People])

 

[KenHigg]

Glad to help.


ken

 

[EMP]

The short time field is [Hours] and the number field is [people] I have been trying the following but no luck. Abnyone any ideas?

Hours_Lost: [People]*(CDbl([Hours])*24)

...........
........... it works fine now.

Hours_Lost: ((DatePart("n", Hours) / 60) + DatePart("H", Hours))*(me.[People])

Since a date/time field is internally a double precision number in days and there are 24 hours in a day, I believed the first expression should also work. It should even work without the need of the CDbl() function.

To test what I believed, I put the following query in the attached database:-

SELECT ID, Hours, People,
[People]*(CDbl([Hours])*24) AS Expr1,
[People]*[Hours]*24 AS Expr2,
((DatePart("n",[Hours])/60)+DatePart("h",[Hours]))*([People]) AS Expr3
FROM tblData;

When the query was run, the three expressions all returned the same results.

ID	Hours	People	Expr1	Expr2	Expr3
 1	 2:15	     3	 6.75	 6.75	 6.75
 2	 1:15	     4	    5	    5	    5
 3	 4:30	     5	 22.5	 22.5	 22.5
 4	 4:30	     6	   27	   27	   27
 5	 0:10	     6	    1	    1	    1

No idea what's causing Expr1 not to work on your system though.

^