IIf field 1 contains text1 then write string2

[civileng]

Is it possible to have IIf function which checks if there is a text1 in another field and if true writes text following text1 in to field containing IIf function.

Example(field1 containing text1)

Na avtocesti Maribor - Ljubljana pred priključkom Blagovica v smeri Ljubljane je zaradi prometne nesreče zaprt vozni pas.

I want to check if it cointains red text and if true, write blue text.


field1.................................field2
***v smeri A***.................A.........
****proti smeri B****.........0........

 

[Brianwarnock]

Field2: Iif(instr(field1,"v smeri")<>0,"Ljubljana","")
Should do it

Brian

 

[pr2-eugin]

Hello civileng, Welcome to AWF..

I am not entirely sure if I understand your requirement..

* How do you intend to find the red text, will this be changing for every record? i.e. for the first record is it going to search for "v semeri", the next one "stark"?? Or is it one word search on all records?

* Do you want the Entire sentence Ljubljane je zaradi prometne nesrece zaprt vozni pas. or just the word next to v semeri - Ljubljane??

I think you might need to write a function that will employ the string manipulation functions like InStr, Len, Mid to achieve this.. We would be happy to help, but needs a bit more info..

 

[Brianwarnock]

Hi Paul
I answered the question as put, if it is his only requirement - job done, if not, as I suspect then we need a great deal of more info which hopefully he will realise and provide it rather than us ask loads of questions.

Brian

 

[pr2-eugin]

Brian I think I am getting old, and my eyesight is failing me.. Ha ha.. I did not even realize that you had answered it before me.. Only after getting a message I checked the thread and voila there it was.. Well as we both have agreed more info is needed..

 

[RainLover]

How did the Red text get there in the first place.

 

[Brianwarnock]

How did the Red text get there in the first place.

I think that was to simplify the explanation of what was required.

Brian

 

[RainLover]

I think that was to simplify the explanation of what was required.

Brian

I believe that making assumptions can lead to making a fool out of someone.

Something like that.

 

[Princes11a]

Hello, I think my question has some similarities, so I will borrow this thread.
I also want to make an if function, but my data is in more than one table.

I have a main table, which is filled trough form from others users. In this form there is a dropdown field, which shows data from table2 and stores this data in one field from main table. in FORM there are 2 fields, where users insert date (this information is also shown in main table). I want to make IF function, which will write A or B in last field of the table2 if there were 2 condition
FIRST (A) an item is chosen from dropdown field and the data is entered in the first DATEfield. SECOND (B) item is chosen from dropdown field and both data fields are entered. Also the form is made with autonumber field (for every customer new one). The form also contains 4 dropdown fields, which contains data from table2 (but users wont choose the same item in field2 3 or 4 or they will leave it empty).

Thanks for the answer

 

[civileng]

@Brianwarnock thanks but the blue text changes, so i would need to insert text(word) following '' v smeri ''. ''Ljubljana'' was just an example, its a city name.


@pr2-eugin
''v smeri'' is constant and code provided by brianwarnoc finds it. I want the first next word to be inserted (possibly two but one should be ok for my problem).

'' v smeri '' mens in direction of and is followed by city name which i would like to extract and populate new field.

Here is a screenshot. shrani.si/f/3K/N9/259jhwvI/access2.jpg

 

[pr2-eugin]

Okay copy the following function into a module..

Public Function getNextWord(txtToSearch As String, toFindWord As String) As String
[COLOR=Green]'-----------------------------------------------------------------------
'   A function that will take in two arguments string to search for and
'   the pattern string to look at. If the pattern matches, the function
'   returns the immediate word after the pattern. Else a NullString.
'
'   Input : String to look for, RegEx String
'   Output: Nothing - If no match, else the next word after the match
'
'   Example:
'       ? getNextWord("Na akom Blagovica v smeri Ljubljane je zaradi pas.","v smeri")
'         Ljubljane
'       ? getNextWord("Na akom Blagovica Ljubljane je zaradi pas.","v smeri")
'         
'-------------------------------------------------------------------------[/COLOR]
    Dim wordPos As Integer, nextWordStart As Integer, tmpstr As String
    wordPos = InStr(txtToSearch, toFindWord)
    If wordPos <> 0 Then
        nextWordStart = wordPos + Len(toFindWord)
        tmpstr = Trim(Mid(txtToSearch, nextWordStart))
        getNextWord = Mid(tmpstr, 1, InStr(tmpstr, " "))
    Else
        getNextWord = vbNullString
    End If
End Function

In Query just use..

CityName: getNextWord([fieldWithLongString], "v smeri")

 

[civileng]

pr2-eugin Thank you very much for fast answer, it works.

 

[pr2-eugin]

Glad to hear that.. Good luck..