Converting from Unix Time to Other Date/Time Formats

[skipmartin]

I am working in msaccess and trying to take a field from a third party database and convert it to a date/time format. I am currently using the following:



Date: Format(DateAdd("s",DATA_HISTORY_HAGLOG!login,"1/1/1970 12:00:00 AM"),"m/dd/yyyy")



Signon_Time: Format(DateAdd("s",DATA_HISTORY_HAGLOG!login,"1/1/1970 12:00:00 AM"),"hh:nn:ss AM/PM")



Signoff_Time: IIf(DATA_HISTORY_HAGLOG!logout Is Null,"No Signoff",Format(DateAdd("s",DATA_HISTORY_HAGLOG!logout,"1/1/1970 12:00:00 AM"),"hh:nn:ss AM/PM"))



Logged_On_Duration: IIf(DATA_HISTORY_HAGLOG!logout Is Null,"00:00:00",Format(DateAdd("s",(DATA_HISTORY_HAGLOG!logout-DATA_HISTORY_HAGLOG!login),"1/1/1970 12:00:00 AM"),"hh:nn:ss"))



The problem is that it is converting to a text field. How can I represent as:



Date: 07/02/2003

Signon_Time: 12:30 PM

Signoff_Time: 12:45 PM

Logged_On_Duration: 00:15



Thanks in advance.

 

[Russell Hauf]

Take out the Format function from each example. However, in the case where you have "No Signoff", you may want to put Null instead (and make sure that a value is not required in the field) as a Date field cannot take both Dates and text values. I also suggest that you rename the field you call "Date" to something else to avoid confusion down the road.

For example:

Signon_Time: DateAdd("s",DATA_HISTORY_HAGLOG!login,"1/1/1970 12:00:00 AM")

HTH,

Russell