convert text from a field in access in a query

Mark Drelinger
Mark Drelinger used Ask the Experts™
on
I have a table in access with a field "FileDate".
The data in that field is like (two sample lines)

"201078573470 2/24/2010 11:02:00 AM"
"201083243410 10/7/2010 10:30:00 AM"

I need to separate out the month / day / year in a query.
The result would be "2/24/2010" and "10/7/2010"
Is this possible?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Retired IT Professional
Commented:
Try using:
Mid(FileDate, 13, 8)

If the first part is not fixed length then you need to check for the position of first / in the string.
In general:
Mid(FileDate, instr(1,FileDate,"/")-2,instr(instr(1,FileDate,"/"),FileDate, " ")-instr(1,FileDate,"/")+2)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial