vba > subtracting time

[metokushika]

how do i subtract time??

i'm trying to make a time sheet that could
calculate the number of hours. like say
i timed in at around 8:00 AM and i
timed out at around 3:00 PM. how do
i get the number of hours that i consumed
for that day?




i was thinking that if i do this > 12 - 8 = 4 then
add the 3 which makes it 7. but the value doesn't
work bec. i figured that you can't subtract an INTEGER
to time. the answer doesn't come up right.

OR maybe if i assign a value to each of the time,
do you think it will work??

EX. If Me.Text67.Text = "2:00 AM" Or Me.Text67.Text = "2:00 PM" Then
Text67.Value = 2



please help please help

[ssanfu]

We need more info.

What is the data type of the fields in the table? (date, text, integer)
What are the field names?
If the data type is Date, then you would use the DateDiff() function.

If the data type is text, integer or the controls on the form are unbound,
then it is more involved in doing the calculation.

BTW, rarely do you need to use the Text property of a control. The text property is actually the uncommitted (new) data of a control as opposed to the Value property (old data).

[trevor40]

i am using the following in a query. it's still a big work in progress but you may get an idea from it.

SELECT [Employee Time Report Output].Expr1 AS [Date], [Employee Time Report Output].[Scan Day] AS [Day], [Employee Time Report Output].Expr2 AS [IN], [Employee Time Report Output].Expr3 AS OUT, [Lunch Tracking].[Lunch Start], [Lunch Tracking].[Lunch End], Format([tt],"h")*60+Format([tt],"nn") AS Expr5, [Employee Time Report Output].[Total Hours less Lunch (30min)], [Total Minutes]-[expr5] AS Expr4, [Employee Time Report Output].["Time and a Half "], [Employee Time Report Output].["Double Time"], [Employee Time Report Output].["Total Hours "], [lunch end]-[lunch start] AS tt
FROM [Lunch Tracking] RIGHT JOIN [Employee Time Report Output] ON [Lunch Tracking].[Time at Lunch] = [Employee Time Report Output].Expr1;