Access VBADiscuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
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?
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.
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?
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.
Convert Text to Date/Time in Access
Linear Mode
