updating the date format in a table?

[Darla]

Hi everyone. I have a table with several fields that contain either a date or text. The current format is mm/dd/yyyy if the value is a date. How do I change the date format to mm/dd/yy without messing with the field if it contains text instead of a date?

[JoeM]

In Access, you cannot have mixed data types within a single field. Each field can only be a single data type.
If you look at the Properties of the table, it will list the data type for each field. I am willing to bet that this field you are talking about is set to "Text", or else you wouldn't be able to have non-date Text entries in it.
So that means that each of your "date" entries is really text, so you can use Text functions like LEFT, RIGHT, and MID on it.

I recommend using an IIF function along with ISDATE to check to see which of these text entries are valid dates, and doing the calculation on those.
That formula would look something like:

IIF(IsDate([MyField]), Format(CDate([MyField]),"mm/dd/yy"),[MyField])

[RuralGuy]

This link helps to understand how Access actually stores the DateTime field: https://support.microsoft.com/en-us/kb/130514