Dividing time by another field

[Wes28]

I have one field in my query that pulls the time diff. In Start and End time.

My issue is getting the time to divide by the amount of parts. To see how long it takes per part.

What I have tried so far.
PerPart: Format([Start]/[TimeDiff],"Short Time")


But the result just shows a #Error..


Thinking this might help.

SELECT tblFinishing.EmployeeNumber, tblFinishing.ReJobNumber, tblFinishing.ReOperationNumber, tblFinishing.ReSubCode, tblFinishing.StartTime, tblFinishing.Start, tblFinishing.Good, tblFinishing.Scrap, tblFinishing.EndTime, Format([StartTime]-1-[EndTime],"Short Time") AS TimeDiff, Format([Start]/[TimeDiff],"Short Time") AS PerPart
FROM tblFinishing
GROUP BY tblFinishing.EmployeeNumber, tblFinishing.ReJobNumber, tblFinishing.ReOperationNumber, tblFinishing.ReSubCode, tblFinishing.StartTime, tblFinishing.Start, tblFinishing.Good, tblFinishing.Scrap, tblFinishing.EndTime
HAVING (((tblFinishing.ReJobNumber) Like "*" & [Enter Job Number] & "*") AND ((tblFinishing.Start) Is Not Null))
ORDER BY tblFinishing.ReSubCode;

Any help would be great.

Thanks.

 

[namliam]

What field (type) is the TimeDiff?
What field (type) is the Start?

 

[Wes28]

TimeDiff = is an expresion I enterd into the query it's

TimeDiff: Format([StartTime]-1-[EndTime],"Short Time")

Start = Number field from a table.

 

[namliam]

1) You shouldnt reuse aliased columns within the same query
2) Format makes TimeDiff a Text field, cant calculate with text fields.

Instead of: PerPart: Format([Start]/[TimeDiff],"Short Time")
Try: PerPart: [Start]/(StartTime]-1-[EndTime])

Try to not force the format in the formula instead put a format on the column, right click the grey bar above the PerPart column in query design you can set formats there.

 

[Wes28]

Thanks Namliam,


I'm getting an answer now. But the problem I get now is it's showing the Hour in each answer. Any idea how to remove this?

Answer is showing as 12:01

Should be just 00:01

 

[namliam]

00:01 doesnt exist, 12:01 is 'right' for a time format thing....
Offcourse I can see how for a 'time elapsed' it is not very usefull unfortunatly

Perhaps if you only have minutes you can use a format of NN:SS or NN ??

Offcourse that runs into problems for hours, but...

 

[Wes28]

Oh man, not the But....

 

[Wes28]

I see. After reading what you wrote. Took me a little to get what you were pointing at. Very nice. I'm on track, Thanks for your nudge.

 

[gemma-the-husky]

wes

bear in mind that times in access are all modulo 24 hours

ie 7hrs+6hrs+12hrs (25hrs) results in an answer of 1hr.

there's lots of potential for errors to slip through when you start manipulating native time data types