Converting a text field to a date field

[cabletek]

I have a field that was imported from a text file that has dates in it. The problem is that it is in the following format: DDMMyyyy and displayed like 11Oct2002 (single digit days displays like 1Oct2002). Because there is no delimiter, it will not import as a date field so it has to be a text field. So how do I convert it to a date field?

 

[danvlas]

You'll have to split it...

Function SplitMyWrongField(StrValue)

If Len(StrValue)< 9 Then
SplitMyWrongField = Format(Left(StrValue,1),&quot;00&quot & &quot; &quot; _
& Mid(StrValue, 2, 3) & Right(StrValue, 4)
Else
SplitMyWrongField = Left(StrValue,2) & &quot; &quot; _
& Mid(StrValue, 3, 3) & Right(StrValue, 4)
End If

End Function

This would split your string into something like:
01 Oct 2002

HTH

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[cabletek]

I assume this has to be done with a Module? I new to programming!

 

[danvlas]

Yes, paste the code into a module, then save the module by any name you want except for the function name
Then use the function as if it were built-in. For instance, in a select query insert anew field and:

FieldName: SplitMyWrongField(YourField)

Run the query and...voila...

Good luck


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[cabletek]

The script work partially, it splits the day & month but leaves the month/year together (10Oct2002 to 10 Oct2002)??? Ralph Solomon
Comcast Cable Communications
ralph_solomon@cable.comcast.com

 

[danvlas]

Oops...quite right...forgot to include a space...

If Len(StrValue)< 9 Then
SplitMyWrongField = Format(Left(StrValue,1),&quot;00&quot & &quot; &quot; _
& Mid(StrValue, 2, 3) & Right(StrValue, 4)
Else
SplitMyWrongField = Left(StrValue,2) & &quot; &quot; _
& Mid(StrValue, 3, 3) & &quot; &quot; & Right(StrValue, 4)
End If

Guess it's fixed now...


Daniel Vlas
Systems Consultant
danvlas@yahoo.com