if text contains '''' then

[civileng]

I have a querie that contains column1 with text(description of incident maybe weather info). I would like to create new column which would search throug column1 and then insert certain word into this new column if searh is sucessful. For example if column 1 contains word snow then snow if colum1 contains word foggy then fog.

colum1................ ..........newcolumn
crash reason snow... ......snow
slow traffic, foggy ..........fog

What is the simplest way to solve this problem?

 

[jdraw]

Please provide in plain English a description of the purpose of the data base. Your question does not have any context, so any answer will not be focused. Readers need to understand your issue/opportunity before providing specific response.

 

[CJ_London]

I think what the poster is saying is that he has a table with two fields (for the purposes of this thread, there could be others) - Description and Reason

What he wants is a routine which scans through the description field and populates the reason field

So if the description says "The weather was cold and snowing" a scan would identify the word 'snow' and populate the Reason field

However I agree this does not go far enough to explain what is required. What happens if the the description is "It was foggy and snowing hard" - which word do you want? Fog or Snow, the first one, last one or both?

Civileng, please confirm or build your explanation from here:

 

[civileng]

jdraw, the purpose is to create new column which extracts part of text from another column (extract weather from incident description) in such a manner that it could be easy to filter or group incidents by weather.


CJ_London yes that is what I was saying.
I want to extract info from column1 (short description of higway incident which may or may not contain weather info) and then populate new column. This new column would be weather column.

If description was foggy and snowing(i think there isn't...i filterd quickly just this column) i would want both - fog and snow

But it should search for just a part of the word...becouse in column1 it could be stated rain, raining; becous of fog, foggy.

I include screenshot on following link(new user..cant post images): tinyurl.com/cn7f2nt

 

[CJ_London]

I think the way to solve this is to create a table and write a bespoke function which would be along the following lines

The table (we'll call it ReasonsTbl) will have two text fields: Search and Response

In this you put the data you require e.g.

Search Response
Fog Fog
Foggy Fog
Snow Snow
Snow Snowing
etc

And the function is as follows:

Public Function FindReasons(Source as String) as String
Dim Rst as RecordSet
Dim S as String
 
    S=""
    set rst=currentdb("Select * FROM ReasonsTbl")
    while not rst.eof
        if instr(Source,rst.fields("Search")<>0 then S=rst.fields("Response") & "; "
        rst.movenext
    wend
    Set rst=nothing
    FindReasons=S
 
End Function

And in your query use FindReasons(Column1) where column1 is the name of your description field

 

[civileng]

i get runtime error 3265.
It also said that there is a seperator missing in line (I added an extra ) behind zero and then got error 3265)

 If InStr(Source, Rst.Fields("Search") <> 0) Then S = Rst.Fields("Response") & "; "

SC1:tinyurl.com/crfqv7p
sc2:tinyurl.com/cqvzw2m

 

[pr2-eugin]

Try..

Set rst = CurrentDB[B].OpenRecordSet[/B]("ReasonTbl")

 

[CJ_London]

Thanks Paul, you are correct

 

[civileng]

CJ_London, thank you very much it works like a charm. And to you as well pr2-eugin.

 

[CJ_London]

Great! Glad to help.

Just thought of one refinement to your ReasonTbl if you haven't already done it.

Index the Search field and set it to no duplicates to ensure there is only one option