Working with text and dates

[Matt_at_Target]

I have a situation where I need to extract the date from a text field to use in a select query. The date in the text field is listed at the beginning of the text field like this.

S12345678 3/21/2008 adittional text beyond here blah, blah...

the first series of numbers can be either 9 or 10 digits long, and then the date is always in this format, the length of the characters change accordingly with the date. So trying to use the Mid function only wont work.

I supposed I need to find a way to get the position of the space character in front of and after the date to use the Mid function for each record.

I need to be able to extract this date to do a DateDiff against another date.

I have been trying to do something like this.

Narr_Date: Format(Mid([si_narr_t],InStr(1,[si_narr_t],Chr(32)),InStr(9,[si_narr_t],Chr(32))),"Short Date")

But I know I am off the mark here. Can someone tell me what I am missing?

 

[EMP]

Try this:

CDate(Left(Mid([si_narr_t],Instr([si_narr_t]," ")+1), Instr(Mid([si_narr_t],Instr([si_narr_t]," ")+1)," ")-1))

^

 

[neileg]

Not elegant bu this should work

Narr_Date: CDate(Left((Right([si_narr_t],Len([si_narr_t])-InStr([si_narr_t]," "))),InStr((Right([si_narr_t],Len([si_narr_t])-InStr([si_narr_t]," ")))," ")))

 

[Matt_at_Target]

Working

This seems to have worked nice and clean. Much thanks