How to convert Dates stored as text into Date/Time

[Alan Englefield]

Hello there,

I have inherited a DB with several fields that have the date stored as
text in the format "dd.mm.yy".

When I change the field type to Date/Time, access throws a hissy fit
saying that it will delete all of the record entires for these fields.

Is there a way that I can do a one-off conversion of all of the data
in the table either separately or field-by-field?

I got the following code from a newsgroup but I don't know how to
implement it as it is a function;
I can link it to a button on a form so that it runs, but I don't know
how to reference the fields that I need to change:

Public Function StrToDate(strIn As String) As Variant
If Len(strIn & "") Then
StrToDate = cDate(Mid$(strIn, 3, 2) & "/" & _
Left$(strIn, 2) & "/" & Right$(strIn, 4))
Else
StrToDate = Null
End If
End Function

Any help would be appreciated and step-by-step help would be
worshipped

Thanks,
Alan

 

[Tom van Stiphout]

On Wed, 23 Sep 2009 06:04:00 -0700 (PDT), Alan Englefield

First you must adjust the function for your pattern:
Public Function StrToDate(strIn As String) As Variant
If Len(strIn & "") Then
StrToDate = CDate(Mid$(strIn, 4, 2) & "/" & _
Left$(strIn, 2) & "/" & Right$(strIn, 2))
Else
StrToDate = Null
End If
End Function

Then I would create a new field in your table, and give it a datetime
datatype. Then write an Update query along these lines:
update myTable set myField = StrToDate(myTextField)
(of course you change myObjectNames to yours)

-Tom.
Microsoft Access MVP

 

[John Spencer]

I would use a function more like the following. It should take care of most
instances of bad data. The function below should work with a string that
exactly matches your stated format.

Public Function fStrToDate(strIn) as Variant

If Len(Trim(strIN & "")) <> 8 then
fStrToDate = Null
ElseIf IsDate(Replace(strIN,".","-")) = false Then
fStrToDate = Null
Else
fStrToDate = DateSerial(Right(strIn,2),Mid(strIn,4,2),Left(strIn,2))
End If

End Function

Add that function to a VBA module and save the module with a different name
than fStrToDate.

Add a NEW COLUMN (field) to your table to hold the calculated correct value OR
just use the function in a query when you need an actual date. In a select query

Field: RealDate: fstrToDate([StringDateField])

If you want to store the real date then add a new field and use an update query.

UPDATE [YourTable]
SET [RealDateField] = fStrToDate([YourTable].[StringDateField])
WHERE [RealDateField] is Null and [StringDateField] is not Null

In query design view
== Add your table
== Add realdatefield and stringdatefield to the query
== Select QUery: Update from the menu
== in the Update To for the realDateField enter
fStrToDate([YourTable].[StringDateField])
== Add the criteria if you wish.
You can do multiple fields at one time by adding them into the query. In that
case you can leave out the criteria or use a separate criteria line for each
set of date fields.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Alan Englefield]

On Wed, 23 Sep 2009 06:04:00 -0700 (PDT), Alan Englefield


First you must adjust the function for your pattern:
Public Function StrToDate(strIn As String) As Variant
    If Len(strIn & "") Then
        StrToDate = CDate(Mid$(strIn, 4, 2) & "/" & _
          Left$(strIn, 2) & "/" & Right$(strIn, 2))
    Else
        StrToDate = Null
    End If
End Function

Then I would create a new field in your table, and give it a datetime
datatype. Then write an Update query along these lines:
update myTable set myField = StrToDate(myTextField)
(of course you change myObjectNames to yours)

-Tom.
Microsoft Access MVP











- Show quoted text -

Tom,

Thanks for you assistance; it is much appreciated.

Alan

 

[Alan Englefield]

I would use a function more like the following. It should take care of most
instances of bad data.  The function below should work with a string that
exactly matches your stated format.

Public Function fStrToDate(strIn) as Variant

    If Len(Trim(strIN & "")) <> 8 then
       fStrToDate = Null
    ElseIf IsDate(Replace(strIN,".","-")) = false Then
       fStrToDate = Null
    Else
       fStrToDate = DateSerial(Right(strIn,2),Mid(strIn,4,2),Left(strIn,2))
    End If

End Function

Add that function to a VBA module and save the module with a different name
than fStrToDate.

Add a NEW COLUMN (field) to your table to hold the calculated correct value OR
just use the function in a query when you need an actual date.  In a select query

Field: RealDate: fstrToDate([StringDateField])

If you want to store the real date then add a new field and use an updatequery.

UPDATE [YourTable]
SET [RealDateField] = fStrToDate([YourTable].[StringDateField])
WHERE [RealDateField] is Null and [StringDateField] is not Null

In query design view
== Add your table
== Add realdatefield and stringdatefield to the query
== Select QUery: Update from the menu
== in the Update To for the realDateField enter
    fStrToDate([YourTable].[StringDateField])
== Add the criteria if you wish.
You can do multiple fields at one time by adding them into the query.  In that
case you can leave out the criteria or use a separate criteria line for each
set of date fields.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello there,

I have inherited a DB with several fields that have the date stored as
text in the format "dd.mm.yy".

When  I change the field type to Date/Time, access throws a hissy fit
saying that it will delete all of the record entires for these fields.

Is there a way that I can do a one-off conversion of all of the data
in the table either separately or field-by-field?

I got the following code from a newsgroup but I don't know how to
implement it as it is a function;
I can link it to a button on a form so that it runs, but I don't know
how to reference the fields that I need to change:

Public Function StrToDate(strIn As String) As Variant
    If Len(strIn & "") Then
        StrToDate = cDate(Mid$(strIn, 3, 2) & "/" & _
          Left$(strIn, 2) & "/" & Right$(strIn, 4))
    Else
        StrToDate = Null
    End If
End Function

Any help would be appreciated and step-by-step help would be
worshipped

Thanks,
Alan- Hide quoted text -

- Show quoted text -

John,

Thanks for your step-by-step breakdown; I now worship you (as
promised)

Alan