Hi,

   I have been given a mess of data, and DOB and other date values are in Text like this DDMMYYYY. When I use the Table designer to convert the Date/Time, Access can't change the values.

   Is there a script or some other function that will allow me to change this data?

Thanks,
mmcdonal

mmcdonal

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

I'd run an update query to convert the string field data into a date value and place the data in a new field defined as a date data type after the data is in the table as a string. CDate should do the trick except that it thinks mmddyyyy. It's generally smart enough to realize that:

23022001

means February 23, 2001, but

03022001

will be interpreted as March 2, 2001.

You can write a format conversion function and return the date datatype for your update:

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

The function returns variants in order to handle nulls in case the field has no data.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Another option you could do, is (and this is only really practical if it's a once off thing) change your computer's regional settings, so that the date is formatted as ddmmyyyy.
This should allow it just fine.
Being an Australian, this is how we do dates here anyway, which causes some problems, as SQL only likes mmddyyyy, so I have to do conversions if I'm building SQL from values in text boxes and the like.
So although the Query Designer shows 31/12/2001, the SQL Statement will show 12/31/2001 anyway.

But if it's something you're going to have to do a lot, then Jürgen has you on the right path

I am a loud man with a very large hat. This means I am in charge

I have the dates in the proper format, but they are stored as text, with a date input mask. DO I need to covert them to Date/Time in order to do date arithmetic with them?

mmcdonal

Access does a great many datatype conversions implicitly. Whether it will produce the result you are seeking depends on the date arithmetic function and that is not tolerable.

Date/Tive fields are of type double with the integer portion translating to a date, and the fractional portion to the time. Adding 1 increments the date by one day. Subracting .5 decrements the time by 12 hours. That will reduce the day by one if the date/time in the variable is before noon. Functions like DateAdd(), Month() DateDiff() generally require a date time datatype to operate on but, assuming the sring format can be interpreted as a date, all that is necessary is to add '#' delimiters around the string type.

The best and only sure way to proceed is to store as a date/time field.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Looks like the answer was "yes".. did you undertand it?

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

I have blocked this email address from my email. You are one tenacious smug bastard.

mmcdonal

Oh no! I've been blocked (how did he do that? he doesn't know my email address)...

More likely you blocked the WROX site.

I won't comment on if that's good or bad.


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee