TIME AND DATE CALCULATION

[johnhobbis]

Hi i hope someone may be able to help

I have field called Discharge time which needs to be [Discharge
time] hh::mm dd/mm/yyyy in a table called TABLE1 then
an other field called [Time used] hh:mm dd/mm/yyyy in a different table
called TABLE2

I need to pass the "time difference" between the 2 fields to a field called
TIME DIFF in Table1


so ....

A Table = table1.Field=Discharge Time [formatted] with hh:mm dd/mm/yyyyy
B Table = table2.Field=Time Used [formatted] with hh:mm dd/mm/yyyyy

RESULT = Table1.Field=TIME DIFF (A-B) in hours and minutes.

but if it longer then 24 hours....... >>???

and i need there result to be passed into the TIME DIFF FIELD

IF ANY of the ACCESS Expert can give me a hand it would be appreciated.

DOES THIS NEED TO BE DONE IN A REPORT - FORM...?

John H.

Mail me or post me...........


john@hobbis.karoo.co.uk.

 

[Rich@ITTC]

Hi John Hobbis

Please do not do multiple postings in different areas of the forum. It makes life difficult for the people trying to advise/help.

Rich Gorvin

 

[Ron Bell]

Hi:
Hope this helps using queries:

create query;1 add Table1 and Table2, with unique ID; + [ Discharge_Time] and [Time_used] from the two tables
create query2; add query1, and use unique ID, + this as other field:-
Diff: Format(DateSerial(Year([Time_used]),Month([Time_used]),Day([Time_used]))-DateSerial(Year([Discharge_time]),Month([Discharge_time]),Day([Discharge_time])),"Standard")
Crieteria = >1
This should give difference between fields dates more than 1day

Getting this data into your DIFF field is another matter.
This could be done by a form or by VBA recordset, as query2 is not updateable you cannot run a update query direct it seems
Try this and let me know how it progresses

Take care to enter the minus sign in query ie
([Time_used]))-
on this page it may seem like another "-"

[This message has been edited by Ron Bell (edited 04-05-2001).]