Date Conversion

[runals]

Hello,


I have a text field with a date like "Tuesday 14 Apr '09". I need to either change that to a standard date via a query, or be able to sort on records that have dates between X and Y. Can someone help me to make the conversion?


Thnaks in advance

Andrew

 

[Remou]

What is the field type - Text or Date/Time? In other words, is the date simply formatted to appear as it does?

http://lessthandot.com

 

[HarleyQuinn]

If it is indeed a text field then you could use a UDF, something like (this has NO error handling but should give you an idea):

Public Function ConvertDate(varDate As String) As Date
Dim arrTemp() As String

arrTemp = Split(varDate, " ")

ConvertDate = DateSerial(CInt(Replace(arrTemp(3), "'", "")), month("01-" & arrTemp(2) & "-2009"), CInt(arrTemp(1)))

End Function

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[SkipVought]

Hi,

Replace the apostrophy with NOTHING.

Strip the Day of Week

Convert the "date string" part to a Real Date.
[tt]
CDate(Right(Replace([YourDateString],"'",""),Len(Replace([YourDateString],"'",""))-InStr(Replace([YourDateString],"'","")," ")))
[/tt]



Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]