Text to Date Conversion in Access

Question

I have a text column in my Access 2007 table of the format "m/d/yyyy hh:mi:ss" i.e. 1-2 digit month 1-2 digit day 4 digit year and "American" date format. The local date format is dd/mm/yyyy.

I want to convert these dates to a Date/Time Field so I can sort on them but when I run the update query using CDate it is inconsistant in its' handling of month and day. It's OK for days > 12 because the date is unambiguous but it converts 1st of August (8/1/2011) to the 8th of January...

I do not have the rights to change my locale - which may be a band-aid if you can do that temporarily.

I can "force" the conversion by doing lots of work with Left, Right, Mid, InStr etc but it's a lot more work than it should be because of the 1-2 digit day and month.

What I would like (but can't find) is the VB equivalent of StrToDate in Borland Delphi/Pascal where you pass in the date string and a format string that tells the conversion what each digit represents.

In Delphi it would be as easy as:-

MyDate:= StrToDate(MyAmericanFormattedDate,'d/m/yyyy hh24:mi:ss');

Is there a VB equivalent?

HansUp HansUp · Accepted Answer · 2012-08-23T07:33:39

There is nothing in VBA like that Delphi function. You can create a function to convert your string values correctly. And you needn't use those Left, Right, Mid, and InStr functions. The Split() function has been available since Access 2000, so you can split out the date parts and feed them to the DateSerial() function.

Public Function DateFromAmericanFormat(ByVal pIn As String, _
        Optional ByVal pDelimiter As String = "/") As Date
    Dim strDate As String
    Dim strTime As String
    Dim dteReturn As Date
    Dim astrFirstSplit() As String
    Dim astrDateParts() As String
    Dim intMonth As Integer
    Dim intDay As Integer
    Dim intYear As Integer

    astrFirstSplit = Split(pIn, " ")
    strDate = astrFirstSplit(0)
    strTime = astrFirstSplit(1)

    astrDateParts = Split(strDate, pDelimiter)
    intMonth = CInt(astrDateParts(0))
    intDay = CInt(astrDateParts(1))
    intYear = CInt(astrDateParts(2))
    dteReturn = DateSerial(intYear, intMonth, intDay) + CDate(strTime)
    DateFromAmericanFormat = dteReturn
End Function

That is only a rough outline. It will fail on a Null input with "subscript out of range". So this may need refinement, but hopefully it's a reasonable starting point.

Text to Date Conversion in Access

1 Answers