Searching a word in a string

  • Thread starter Thread starter radiaz via AccessMonster.com
  • Start date Start date
R

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
 
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] & "*"
 
Back
Top