CDate is the function that you want. It will convert text string that can be
evaluated as a valid date/time into an actual date/time.
The problem is that CDate doesn't play well with non-valid dates. Therefore
I usually check with IsDate first and handle bogus dates. Something like
below will convert valid dates and put in a bogus 1/1/1950 for non-valid date
strings or nulls.
SELECT IIf(IsDate([date])=True,CDate([date]),#1/1/1950#) AS NotDate
FROM tblDatesText;
However your dates have a problem:
Debug.Print IsDate("Thursday, May 22, 2008 4:54 PM") = False
Whereas
Debug.Print IsDate("May 22, 2008 4:54 PM") = True
So you might need to strip out the day of the week. Assuming that all of the
records have one, something like below would work:
Mid("Thursday, May 22, 2008 4:54 PM",Instr("Thursday, May 22, 2008 4:54 PM",
" ")+1)
Therefore it might take something like:
SELECT IIf(IsDate(Mid([date],Instr([date], " ")+1))=True,
CDate(Mid([date],Instr([date], " ")+1)),
#1/1/1950#) AS NotDate
FROM tblDatesText;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Nico said:
The date and time is stored as text, for example, as "Thursday, May 22, 2008
4:54 PM", but I need to query from it so I need it in a recognizable
date/time format.
Any advice on how to convert it?
Thanks!