Convert a Text to a DateTime in access

Question

So i have a field that datatype is a text which is feed into the database. What it returns is something along the lines of this:

ddd MMM dd hh:mm:ss yyyy

What i would like for it to do is be displayed as something like this:

ddd MMM dd yyyy hh:mm:ss

I can achive this by using Format() which would look like this:

Format(alarmdet.AlarmStart, "ddd MMM dd yyyy hh:mm:ss) AS AlarmDateTime

So that is all well and good, however; i want to beable to convert this value into a datetime. I've tried using CVDate, CDate, DateValue and every time i get returned an error claiming a mismatched datatype. How would i go about converting this exact string into a datetime?

Note:

So you are aware, i am able to get it to convert successfully when in the English(united states) locale, but i am attempting to get this to work in the Portuguese(portugal) locale. In this locale i get the mismatch datatype error which i think has something to do with how access reads the abrivated months. Is there something i am missing to make this successfully work in an international setting?

Also i would like to convert something similar to this in a different field to have it appear as so:

MM/dd/yyyy

Again i know i can get this using Format(), but i would like to to be converted into a DateTime. How would i go about doing this?

Any help or suggestions are greatly appreciated.

Thanks.

Answer 1

Seems to me the first challenge is reading your custom string as a valid date. In your previous question, you gave this as a sample string stored in your [AlarmStart] field:

Tue Jan 18 10:10:57 2011

The problem is VBA doesn't recognize that string as containing a valid Date/Time value.

? IsDate("Tue Jan 18 10:10:57 2011")
False

However, if you revise that string (drop the day of the week and move year before time), you can produce a string which VBA recognizes as a valid date.

? IsDate("Jan 18 2011 10:10:57")
True

So you can use a function to split apart the string, rearrange the pieces you need, and return a Date/Time value.

Public Function DateFromCustomString(ByVal pIn As String) As Variant
    Dim varPieces As Variant
    Dim strNew As String
    Dim varReturn As Variant

    varPieces = Split(pIn)
    strNew = Join(Array(varPieces(1), varPieces(2), varPieces(4), _
        varPieces(3)), " ")
    If IsDate(strNew) Then
        varReturn = CDate(strNew)
    Else
        varReturn = Null
    End If
    DateFromCustomString = varReturn
End Function

(The Split() and Join() functions are available starting with Access 2000.)

And, once you have a Date/Time value from your string, you can use the Format() function to display it however you like.

Format(DateFromCustomString(alarmdet.AlarmStart), "ddd MMM dd yyyy hh:mm:ss") AS AlarmDateTime
Format(DateFromCustomString(alarmdet.AlarmStart), "mm/dd/yyyy") AS AlarmDate

This works as described with English month name abbreviations. I don't know what will happen with Portuguese.