I have a very large data base (3 million records) that I need to change a number representing the date such as 20010312 into 2001.03.12 and time such as 183900 into 18:39; the last two numbers represent the seconds and are not needed.

The other issue with the time values is that those for 12:01am are in the database as 100 and need to come out as 00:01, again the last two zeros are representing seconds and are not needed.

Any help would be greatly appreciated.

Rather than convert the dates and times to another text format I would substitute the existing text date and time fields with standard datetime datatype fields. This is the best way to store date and time.

They can be displayed as the dotted format where required in queries, forms and reports.

To make the conversion to date time use the Left and Mid and Right functions to parse the string and DateSerial to convert to the datetime format.

DateSerial(Left(datefield,4),Mid(datefield,5,2),Ri ght(datefield,2))

Similar with the time using TimeSerial.