code to convert clock count to date - simple?

[John S]

I have to build some queries based on a table called ASSIGN which stores dates and times as nine digit string as follows:

CALLDAT = 131336708
CALLTME = 269688871

I've been give the following algorithm for converting these to date / time format:

Dat = CALLDAT_HDW
Tme = CALLTME_HDW

YYYY = Int(Dat / 65536)
Mm = Int((Dat - (YYYY * 65536)) / 256)
Dd = Dat - (YYYY * 65536) - (Mm * 256)
Nhour = Int(Tme / 16777216)
nMinute = Int((Tme - (Nhour * 16777216)) / 65536)
NSecond = Int((Tme - (Nhour * 16777216) - (nMinute * 65536)) / 256)

I'm very happy building simple queries - less so with modules & VBA.
Would be very grateful if someone could explain how I take this info and generate a date / time which I can view in a simple report.

thanks
John S

 

[Mile-O]

What are CALLDAT_HDW and CALLTME_HDW and where do they come into it?

 

[John S]

apologies - they are the same as CALLDAT and CALLTME - the actual table and field names are more complex, but I'd tried to simplify things

 

[WayneRyan]

John,

Didn't try to make sense of the formula, or how to use it, but you
were almost there.

Based on what you posted:

CALLDAT = 131336708
CALLTME = 269688871

I've been give the following algorithm for converting these to date / time format:

Dat = CALLDAT_HDW
Tme = CALLTME_HDW

YYYY = Int(Dat / 65536)
Mm = Int((Dat - (YYYY * 65536)) / 256)
Dd = Dat - (YYYY * 65536) - (Mm * 256)
Nhour = Int(Tme / 16777216)
nMinute = Int((Tme - (Nhour * 16777216)) / 65536)
NSecond = Int((Tme - (Nhour * 16777216) - (nMinute * 65536)) / 256)
'
' Add this code ...
'
NewDate = CDate(CStr(Mm) & "/" & _
                CStr(Dd) & "/" & _
                CStr(YYYY) & " " & _
                CStr(Nhour) & ":" & _
                CStr(nMinute) & ":" & _
                CStr(NSecond))

Wayne