More Time Calculations

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have read the web pages that some have referred me to
regarding time calculations, but I still can't get it to
work.

What I have is:

STATUSDATE, a number field that is YYYYMMDD format
AHTIME, a number field that represents time HHMMSS
ESTIME, a number field that represents time HHMMSS

I have a STATUSDATE for each of the AHTIME and ESTIME
fields. Usually the STATUSDATE for each is the same.

Note, none of these fields are in Date/Time format. They
are all Long Integer, Number Type fields. (The data was
retrieved from a different system and imported from a text
file into Access. I can't control how the data was
formatted.)

How can I get the STATUSDATE and the AHTIME in a field by
itself (and the same for STATUSDATE and the ESTIME) that
is DATE/TIME format? Assuming I do this, how then can I
perform a calculation to determine the amount of elapsed
time to be returned in HH:MM:SS format?
 
To turn StatusDate into a date field.


DateSerial(Left(Cstring(StatusDate),4),
Mid(CString(StatusDate),5,2),
Right(CString(StatusDate),2))

To turn AHTime into a time in a date time field

TimeSerial(Left(CString(AHTime),2),
Mid(CString(Ahtime),3,2),
Right(CString(AhTime),2))


Add them together to get a date time and then use DateDiff to get the
difference.

DateX + TimeY - where DateX = the calculated date and TimeY = Ahtime
DateX + TimeZ - where DateX = the calculated date and Timez = EstTime

So,
DateDiff("S",DateX+TimeY,DateX+TimeZ) will give you seconds.

Now you can manipulate that to show a hours, minutes and seconds. CalcSecs =
above formula.

CalcSecs\3600 &":" & (CalcSecs Mod 3600)\60 & ":" & CalcSecs Mod 60

The above formula is a bit shaky, as it is almost midnight, and I've just
finished my second glass of wine.

good luck
 
When trying to perform in an update query the:

DateSerial(Left(Cstring(StatusDate),4),
Mid(CString(StatusDate),5,2),
Right(CString(StatusDate),2))

I get "Undefined function 'Cstring' in expression"

???
 
Back
Top