Text to Date

[patnick]

Could you please help me on this:
I would like to convert theText below into a Date and do not find any solution:

0921679 V301239 E060310 97,6%

The resulting date should be 06/03/2010 which comes from the text after the E with an added 20.... number for the year.

Many thanks for your help in advance!

Cheers!
Patnick
MS-Access 2007

 

[datAdrenaline]

Where do you want the result to be? ... or how do you plan to use the expression/solution?

In a query or control source of a text box on a form:

CDate(Format(Mid([FieldName],InStr([FieldName],"E") + 1, 6),"00/00/\2\000"))

Or... you could create a VBA User Defined Function:

Public Function ParseDate(strStringToParse As String) As Date
    Dim aTemp() As String
    aTemp = Split(strStringToParse, "E")
    If UBound(aTemp) > 0 Then aTemp = Split(aTemp(1), " ")
    If Len(aTemp(0)) > 0 Then _
        ParseDate = CDate(Format(aTemp(0), "00/00/\2\000"))
End Function

With the UDF, you can then use it in your queries or in your control source's in order to keep from contantly having to type that huge expression

 

[gemma-the-husky]

one issue with 6 digit dates is the assumption relating to the century.

are there any residual 19 something dates that you need to handle, or just this century. And its looking a long way ahead, but the same problem is going to recur in 90 years time!

 

[datAdrenaline]

Definately a good point Dave, and if that is a concern, the Format() pattern I suggested could just be set to:

"00/00/00"

Then the CDate() will use the settings on the PC to determin the 19 or 20 in this century, and 20 or 21 in the next, thus removing the hard coded century.

 

[JPaulo]

Or;

MyDate: CDate(Mid([YouField];2;2) & "/" & Mid([YouField];4;2) & "/" & Right([YouField];2))

 

[Brianwarnock]

Or;

MyDate: CDate(Mid([YouField];2;2) & "/" & Mid([YouField];4;2) & "/" & Right([YouField];2))

Sheesh, I hope you read your specifications better than this thread, have you seen what his field looks like?

Brian

 

[raskew]

Perhaps:

x = "0921679 V301239 E060310 97,6%"
y = mid(x, instrrev(x, " ")-6, 6)

? y
060310

z = dateserial(right(y,2), Left(y, 2), mid(y, 3,2))

? z
6/3/2010

Bob

 

[patnick]

Many thanks for your help - all of you.
I tried it the way datAdrenaline proposed and it works great!

You made my day! Thanks a lot!

 

[JPaulo]

Sorry Brian and calm;
I thought that the data came from individual fields, was I wrong to read.