help! - amending 14000 date records

[nashie]

i've got an access database with about 14000 records in it.

theres a date field with the date in the following format:

DD/MM/YY

however this has got to be altered to the form:

DD/MM/YYYY

is there a way i can do a find and replace but only of the last 2 characters of the field?

its a text field, so i need some way of adding in the prefix for 14000 odd records.

i did a search for */02 and told it to replace it with /2003 but it replaced the entire field instead of just the /03.

the only other option is to code some asp to do it, but surely theres got to be a way of doing it in access?

any help would be very much appreciated!!

 

[Fuga]

Put this in a module and run it. Copy the table first just in case!

Sub alterdate()
Dim rs1 As DAO.Recordset
Dim strolddate As String
Dim strdate As String

Set rs1 = CurrentDb.OpenRecordset("yourtable")
rs1.MoveFirst
Do While Not rs1.EOF
    strolddate = rs1.Fields("yourdatefield")
    strdate = Left(strolddate, 6) & Right(strolddate, 2)
    rs1.Edit
    rs1.Fields("yourdatefield") = strdate
    rs1.Update
    rs1.MoveNext
Loop


End Sub

Hope it works!

Fuga.

 

[Tiro Cupidus]

Changed a little bit. Would this work?

Sub alterdate()
Dim rs1 As DAO.Recordset
Dim strolddate As String
Dim strdate As String

Set rs1 = CurrentDb.OpenRecordset("yourtable")
rs1.MoveFirst
Do While Not rs1.EOF
    strolddate = rs1.Fields("yourdatefield")
    If Value(Right(strolddate,2))<10 Then
        strdate = Left(strolddate, 6) & "20" & Right(strolddate, 2)
    Else
        strdate = Left(strolddate, 6) & "19" & Right(strolddate, 2)
    End If
    rs1.Edit
    rs1.Fields("yourdatefield") = strdate
    rs1.Update
    rs1.MoveNext
Loop

End Sub

 

[Tiro Cupidus]

I encountered problems when simply changing the data type with the dd/mm/yy format and a variety of dates. If the dd is from 01 to 12, it assumes you are already in mm/dd/yy. >12, it assumes you are using yy/mm/dd, unless the assumed dd is too great for the month, in which case it thinks you are using dd/mm/yy... which you are! Hurray!. Perhaps there is a way around all that and I just don't know it. I wish those don't-know-it's didn't outweigh the know-it's so much =) Without a doubt, though, you should get that into a date data type.