format field to date

[Tony1258]

I have two tables that I import that have both a date field and a time field formated as text. date field looks like yyyymmdd and time looks like hhmmss. I am using two append queries that combine the data into one table and I need the date and time fields to be date and time format respectively. When I run the query as is I get no values in either the date or the time field due to a data tyoe mismatch. My question is how do I convert the input table date and time text type fields to date and tie format for the append query.

Thanks for the help

Tony

 

[EMP]

Two expressions for converting the text date and time to date and time:-

DateSerial(Left([TextDate],4),Mid([TextDate],5,2),Right([TextDate],2))

TimeSerial(Left([TextTime],2),Mid([TextTime],3,2),Right([TextTime],2))


If your version of Access doesn't have the TimeSerial() function, you can use:-

TimeValue(Left([TextTime],2) & ":" & Mid([TextTime],3,2) & ":" & Right([TextTime],2))


You can use them in your queries to update or append to two date/time fields.

^

 

[neileg]

Don't disagree with EMP, however....

Access doesn't support date fields and time fields, just a date/time. This datatype is actually a number where the integer part represents the date and the decimal fraction represents the time. Thus, if you want to work with your data in the way that Access understands and the way that Access functions can be used you need to combine these two values into a single field.