Date Format : yyyymmdd to mm/dd/yyyy MS Access 2010

What is the data type?

If you are already using the Date data type (best practice), simply go into the table design and change the number format in the column properties.

If it is text, then you could use a query like this:

UPDATE [tablename]
SET [columnname] = Mid([columnname], 5, 2) & "/" & Right([columnname], 2) & "/" & Left([columnname], 4)
WHERE [columnname] Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

Select allOpen in new window

Note that you would also want to find any rows that did not have an entry matching the old format:

SELECT *
FROM [tablename]
WHERE Not [columnname] Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

Select allOpen in new window

However, I think your current format is much better than mm/dd/yyyy, as there would be no ambiguity over the order of the date parts.

If you are using a numeric data type (Long, Single, or Double), you will not be able to store the /s.

The data type is in number, the reason to converting  yyyymmdd to this mm/dd/yyyy format is  there is another date field in the table with mm/dd/yyyy format.
After making both date fields in to same format- I need to compare those two date fields and find out the record which are not same.

Thank You So Much... It Is Working Good