Change 19960101 to Jan 1, 1996?

[RIM-Pro]

How do I change a date entry in a text field to an entry that more resembles an actual date? I've inherited a 2,300 record database where dates have been entered into a text field. The format for these dates is yyyymmdd, or 19960101 for January 1, 1996.

Is there an easy way to change the data that is already in this text field to a more traditional format for dates, without having to rekey it all?

Thaks in advance for any suggestions.

 

[David R]

Do you have the ability to change the field now that you've inherited this, or are you going to continue to get dates in this fashion?

If you can change it, I would devise an update query that uses Left(), Mid(), and Right() to break the date down into its components and reassemble it in a NEW field. (Save a backup first, for your own sanity and ours). Make this field text until you get it all sorted out, then when you're sure the data is valid convert it to date/time. Delete your old field after checking it over.

If you're going to continue to get data in this fashion, your best bet might be a field in a regular select query using the same syntax as above (something like Right([DateField],2) & "/" & Mid([DateField,5,2) & "/" & Left([DateField],4) should work, but check my math) and format it as the way you want your dates to show.

Hope that helps,
David R

 

[Fornatian]

I would echo David R's sentiments and also add that there is a CDate function that you can access via the expression builder that you could integrate into an update query to get your dates in the format you need.

Again I would reiterate that you should take a backup of this data and make sure the local settings on your PC reflect the settings you actually use e.g. UK vs US as this can lead to extraordinary results.

Ian

 

[Steven McEwan]

I would add another field to the table (called "New Date"), with date format. You can then run the code below, substituting the name of your existing date field for "[Old Date]" in the line marked with stars, and the name of your table (in speechmarks) for "Dates" on the three occations it occurs.

If you receive any more dates in text format you can simply run the code again.

Edit: I agree with both the other responses. Defining months in words (as the code below does) avoids the problem mentioned by Ian about UK/US differences. This code uses the CDate function.


>>>>>>>>>>>>>>>>>>>>>>>
Sub ConvertDates()

Dim dbs As Database
Dim rst As Recordset
Dim Month(12) As String
Dim NoRecords As Integer
Dim n As Integer
Dim OldDate As String
Dim OldYear As String
Dim OldMonth As String
Dim OldDay As String
Dim NewDate As String

Month(1) = "January"
Month(2) = "February"
Month(3) = "March"
Month(4) = "April"
Month(5) = "May"
Month(6) = "June"
Month(7) = "July"
Month(8) = "August"
Month(9) = "September"
Month(10) = "October"
Month(11) = "November"
Month(12) = "December"


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Dates")
NoRecords = rst.RecordCount
rst.Close

Set rst = dbs.OpenRecordset("Dates", dbOpenDynaset)


For n = 1 To NoRecords

'***********'
OldDate = DLookup("[Old Date]", "Dates", "ID = " & n)
'***********'

OldYear = Left(OldDate, 4)
OldMonth = Mid(OldDate, 5, 2)
OldDay = Right(OldDate, 2)

NewDate = Month(CInt(OldMonth)) & " " & OldDay & ", " & OldYear

rst.FindFirst "ID =" & n
With rst
.Edit
![New Date] = CDate(NewDate)
.Update
End With

Next n

rst.Close

Set dbs = Nothing

End Sub

[This message has been edited by Steven McEwan (edited 04-03-2002).]

 

[RIM-Pro]

Just wanted to say thanks to those of you who responded to my plea for help, I appreciate it. I did find an answer under the TABLES forum, where I posted the same question.

Thanks again!