Problem with dates....

[RTKarkivi]

Hi guys,

I'm for the first time in this forum.
I have a big problem. Well, I have a database with the aprox.16000 records. The problem is that people who used to entry datas in this database, have done it wrongly. Instead of entrying dates in this order mm/dd/yyyy they have done it dd/mm/yyyy, even if the computer settings were mm/dd/yyyy. Now you can imagine what a mass is it now. I have the dates like this : 02-Apr-2016 etc. Do you know how could I fix this problem.

Would appreciate your help.

Thanks in advance,
Hysen

 

[jrbarnett]

Because of the size of the database, I would take a copy of this before trying this, but try the following:

Open the debug window (Ctrl G)
Type in the immediate window:

DoCmd.RunSQL &quot;Update table set datefield = Format(Datefield, &quot;&quot;mm/dd/yyyy&quot;&quot&quot; <RETURN>

Replace &quot;table&quot; with the name of the table, and Datefield with the name of the field in the table that has the data
then see if this does the trick.

Please remember though that in some circumstances, dates in mm/dd/yyyy and dd/mm/yyyy format are both valid, so it may not pick up everything.

John

 

[RTKarkivi]

thanks jrbarnett,

i tried what u told me, but no success.

Hysen

 

[MichaelintheUK]

Hello Hysen

I wonder if your problem is more difficult to solve. The dates were entered with a US setting but as European type dates. Is this correct? Then I think some would be converted e.g. 1 June becomes 6 January. However I think in some cases Access second guesses and finds the best fit e.g. 20/01/03 is assumed to be 01/20/03.

I would suggest some deep digging into whether the current data is accurate before proceding any further.

Thanks

Michael

 

[jrbarnett]

Following on from Michael's comment, set the format property on the field to Long Date in table design view.
This will display the month name in full rather than the digit and will tell you if it is in there correctly.

John

 

[Norris68]

If it has all been entered consistantly, i.e. all dates that could be interpreted as US have been, it shouldn't be too much of a job to sort it out. If not, then your data is pretty much useless. This is a major bug-bear of mine, being a UK developer! I ALWAYS use a 3-character month when updating dates: e.g. UPDATE table SET MyDate=#01 Jan 1980#.

Make sure your international settings (in Control Panel) are correctly configured - on Windows 9X if you installed as UK, it still uses US dates even though it says it is using UK. Reset the international settings to UK and it will probably want a reboot and then interpret dates correctly.

If your data is consistently wrong, you will need to write a routine to sort it out. Basically what has happened is that Access has interpreted the date as US format if it can - if not, it has used UK format. You will need to swap the day and month values for all dates that have a day value up to 12. e.g. (pseudo-code)

For each MyDate in your database
DayBit = Day(MyDate)
MonthBit = Month(MyDate)
YearBit = Year(MyDate)
'Check you have them the right way round before actually making changes, especially if you have altered your international settings!
If DayBit <= 12 Then
'Swap day & month bits
MyDate = DateSerial(YearBit, DayBit, MonthBit)
Update the date
End If
Next MyDate

If using SQL server you can avoid the US/UK confusion by using ISO notation: UPDATE table SET MyDate={d 'yyyy-mm-dd'}

 

[leocarter]

Hi, I had that problem and i did solve it exporting the table to excel and work with sorting data tool and I got all dates ok. Remember to use an ID field to get the original sorting again to export to Access.