Date Conversion

[daver56]

I have a problem that I need help with.

I have imported data into a access table from another application. There are several date-times stored in the table. In all them the date-time consist of two fields one for the date in yyyy-mm-dd format in a string field, and the time in another field with a string looking like HH:MM:SS. I am pulling this data with a make table query from a linked ODBC database. For example if I look at start date, it will be in two fields "startdate" (formatted as yyyy-mm-dd) and "starttime, (formatted as hh:mm:ss") I would like to change this data to a single field datetime format for access, I will be needing to use this data along with the other date and times to calculate date and time differences. I would like to do the conversion when I first bring the data in to the access table. Any help?

Dave

 

[SkipVought]

Hi,

YourDateTime: DateSerial(mid([startdate],1,4),mid([startdate],6,2),mid([startdate],9,2))+TimeSerial(mid([starttime],1,4),mid([starttime],6,2),mid([starttime],9,2))+)

Skip,

_[red][/red]

 

[SkipVought]

OOPS...

TimeSerial should be...

TimeSerial(mid([starttime],1,2),mid([starttime],4,2),mid([starttime],7,2))

YourDateTime: 
DateSerial(mid([startdate],1,4),mid([startdate],6,2),mid([startdate],9,2)) + 
TimeSerial(mid([starttime],1,2),mid([starttime],4,2),mid([starttime],7,2))

Skip,

_[red][/red]

 

[PHV]

Even simpler:
YourDateTime: CDate([startdate] & ' ' & [starttime])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[daver56]

I think that should work, I will give it a try. How would I use the above to convert the data as it is pulled in through the query. What I started with was a make table query and then I use a append table query for additional data as needed.

I started with a maketable query. I every few days I run an append table query to add the most current data to my main table.

Or would I be better off moving the data to a temporary table then converting the date/time and then updating my main table.

Thanks

Dave

 

[daver56]

I have made the conversion work with "DateValue" on my Make Table Query. I am now having problems with the Update Query. I am getting a Reserved Error (-3002) and it states that there is no text associated with that error. Has anyone see this before. I googled it and could not find anything about that error