Multiplying Currency times elapsed time

[natep26]

I have a query that calculates elapsed time from TimeIn and TimeOut formatted to hours and minutes. I need be able to mulitply the elapsed time by the hourly wage in order to find the hourly pay.

How do you format the time so that it is compatible to multiply with the wage?

My SQL statement is below. Thanks for your help.

SELECT tTimeCards.ValetFirstName, tTimeCards.ValetLastName, Format([TimeOut]-[TimeIn],"hh:mm") AS HoursWorked, [HoursWorked]*[tValets]![HourlyWage] AS HourlyPay
FROM tValets INNER JOIN tTimeCards ON tValets.ValetID = tTimeCards.ValetID;

 

[rainman89]

youd need to do it so it would return somethign in the form of decimals
l

i have code somewhere. let me find it

wait now that i think about it.... just divide by 60

so [timein-timeout]/60

is it possible for someone to work more than one day though?

 

[natep26]

It is possible to carry over to the next day, but not more than 24 hours. The timein timeout fields include the date and time.

If i just divide by 60 it gives me a decimal number but it's not correct as far as telling the number of hours. (it's a really small number)So far they all come out to .00###

 

[rainman89]

thats because its probably the timestamp that is including the date. try this formula

TotalTime: Sum(NZ(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60))

changing the appropriatefieldnames

 

[natep26]

I get the error "You tried to execute a query that doesn't include blahblah as part of an aggregate function"

 

[rainman89]

where are you putting this in your query?

also its possible that you might have to do the hourlypay * totaltime in 2 diff queries

 

[natep26]

SELECT tTimeCards.ValetID, Sum(NZ(IIf([timein]<[timeout],DateDiff("n",[timein],[timeout]),1440-DateDiff("n",[timeout],[timein]))/60)) AS HoursWorked, [HoursWorked]*tValets!HourlyWage AS Expr1
FROM tTimeCards;

 

[natep26]

I got it now...For some reason I didnt need the Nz function. Just the regular ol' Datediff.

Thanks for your help

SELECT tTimeCards.ValetID, tTimeCards.ValetFirstName, tTimeCards.ValetLastName, DateDiff("n",[timein],[timeout])/60 AS HoursWorked, [HoursWorked]*tValets!HourlyWage AS Expr1, tTimeCards.TimeIn, tTimeCards.TimeOut
FROM tValets INNER JOIN tTimeCards ON tValets.ValetID = tTimeCards.ValetID;

 

[rainman89]

nz just forces a value if for some reason one was left blank.

HTH