Searching a word in a string

[radiaz via AccessMonster.com]

Hello,

I have this DB that has over 100,000 rows. I need to search a column
"subject" for this word "I-40".
I've tried the following " like I-40 " and "*I-40", but it returns the whole
string. It also returns all the rows with I-40 in it which is good. I'd like
to get the I-40 yanked out of each row. It would look like

ID Subject
1 I-40
2 I-40
3 I-40
4 I-40
5 I-40
6 I-40
etc I-40
etc I-40
2349 I-40

A sentence in the column "subject" would look like

1.An accident at exit #12 on I-40 South was reported at 18:00 Incident
#209089
2.There was a three car accident this morning on I-40 West causing a long
backup along the Interstate. Incident #23123

I started messing with the Instr(, trim but I'm having no luck. For example,
I do this
Road: Mid([Alerts].[Subject],44,10)
and I get really close but not what I'm looking for. I get this, "I-40 West"
or this "or I-40 Ea"

I did the following Test: InStr([Alerts].[Subject],"I-40") and I get numbers
instead. Here are the results...55, 34,23 etc....


I'd appreciate if someone can direct me to the correct way of doing this.


Thanks
Rita

 

[John Spencer]

It doesn't work quite like that. If you return the field subject, you
are going to get the entire field. Perhaps you want something like the
following.

SELECT ID, [ENTER SEARCH STRING] as FoundThis
FROM [Your Table]
WHERE Subject Like "*" & [ENTER SEARCH STRING] & "*"