Hi All

I need some help with being able to convert a txt field to a date field.
The Field format = txt
The data looks like 070402 (which if noted is yymmdd)

I can format this to look like 07/04/02 with no problems
However as our systems are all set to dd/mm/yyyy the data is incorrect.
The 070402 is imported from a csv, and the 07/04/02 is a result of an update query that reads
UPDATE B2BOrdersStage SET B2BOrdersStage.[Date] = Format(([Date]),"yy/mm/dd");
Date being the name of the field

However no matter what i try i cannot seem to get the date to read format dd/mm/yyyy eg 02/04/2007
Can anyone help

Ta

Use cDate and string manipulation to get the value changed to a proper date format:
------------------------------------------------------------------------------------------
MyTable.[Date Field Name] = cDate(Mid(sImportDate, 3, 2) & "/" & Right(sImportDate, 2) & "/" & Left(sImportDate, 2))
------------------------------------------------------------------------------------------
Format doesn't change or convert values, it only changes how it is displayed.

Use a combination of the LEFT, RIGHT, and MID functions with the DATESERIAL() function.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division