change text to a date in a query

[deb]

Using Access2K.

The data imported into a table looks like this...

White| |Greg|20oct2002|

I used a query and parsed it into individual fields using formats simular to the one below.

WEDate: Left$([WEDate2],InStr([WEDate2],"|")-1)

After parsing, parsing and parsing again.
I now have 3 fields...
White and Greg and 20oct2002

The problem now, is that the date is in text format.

How can I make the query read this field as a date?

Thanks in advance,

 

[bat17]

If you wrap the function that you used to get the date with the CDate() function it will force it into a date for you.

HTh

Peter

 

[deb]

I tried...
WEDate: CDate(Left$([WEDate2],InStr([WEDate2],"|")-1))

and the query fields show as #Error.

What did I do wrong?

 

[bat17]

It looks as if Access does not recognise 20oct2002 as a date.
You will need to split it further
it will probably be easier to do this as a secon step rather than try to combine into one long function!
dDate: =CDate(Left([WEDate],2) & " " & Mid([WEDate],3,3) & " " & Right([WEDate],4))
This assumes that your date has a ddmmmyyyy format.

HTH

Peter

 

[Corticus]

Hi again deb,

Add another expression to the query that I showed in this post:
http://www.mrexcel.com/board2/viewtopic.php?t=61572

Use something like this:
Date: Format(Left([txt_date],2) & "/" & Right(Left([txt_date],5),3) & "/" & Right([txt_date],4),"mm-dd-yyyy")

Here's an example:
http://www.theillumni.com/posts/delimit.zip

Hope this helps,

 

[deb]

You are awsome again,

Thanks for all of your help today.