Inconsistent Date Format Problem

[LLB]

I have a table with several date fields. All the fields are formatted to medium date, but the data is being saved in to different ways. Some records are yy-mmm-dd and some are dd-mmm-yy. Each field has at least a few inconsistent dates but not always in the same record. I can't figure out how to make them consistent again. Has anyone else ever had this problem?

 

[Dennisk]

the dates themselves are held as a number and the fractional part is the time.
However some dates may be entered from a PC where the regional date is set incorrectly. You wouldn't beleive the number of PC I've seen that have been set up with the date in USA format.

What I would do is to create a query and see if you can develop a unified date format. If so then you could store the dates along with the primary key of each row in a new table (use a text datatype so you can see exactly what is stored) then use that to update the main table.

 

[neileg]

I don't disagree with Dennisk, but there may be more going on. As Dennisk says, date/time data in Access is stored as a number with the integer part denoting the date and the decimal fraction giving the time. If all of your dates are recognised as dates and thus stored as a number, all you have is a formatting problem. Applying a new format in a query or form should render them as a consistent time format.

However, if they have been stored as text, it's a whole new ball game.

It seems odd that, post Y2k, you have two digit years. If they had been 4 digit it would have been easier. Do you have ambiguous dates such as 28-Mar-31? Could be 31st March 1928, 28th March 1931, or even 31st March 2028 or 28th March 2031!