First word in string

[Eljefegeneo]

I want to compare two large lists where the Organization name is the same. Unfortunately the names may not be exactly the same thus I am attempting to parse the first word in each list against the other.

Thus I thought if I could get the first word in a string that I could at least determine if there was any similarities in the two lists when checking for duplicates.

The only problem is that some strings only have one word and this results in a #Func! Error. I have tried the following in my query:

 Org: Left([tblMain.Organization],InStr([tblMain.Organization]," ")-1)

Org1: IIf(Len([Organization])>0,(Left([Organization],InStr([Organization]," ")-1)),"")

Org2: Nz(IIf(Len([Organization])>0,(Left([Organization],InStr([Organization]," ")-1)),""),"")

All of which give me the #Func! Error.

Then I tried the following which gave a blank when there was only one word.

Org4: Left([Organization],InStr([Organization]," "))

 

[theDBguy]

Try adding an initial IIf() check to see if a space char exists.

 

[arnelgp]

why not deliberately remove Leading/Trailing spaces:

Trim$([Organization])

 

[Eljefegeneo]

Trimming won't work. Trying to see if there is for example, Bob's Burger Barn and Bob's Burgers or Bob's Barn.

 

[MajP]

Solved - Search all words in field against all words in another field

I kind of disagree in a db because you can probably build a much better search using sql Like search. This is based on a pretty thorough demonstration of a couple of interfaces using (Soundex, Simil, Levenshtein, and just find as you type) Like?? Like won't find similarities in strings with...

www.access-programmers.co.uk

 

[Eljefegeneo]

Finally figured out that this will parse the first word of a string and not throw the error message.

Org: IIf(InStr([Organization]," "),Left([Organization],InStr([Organization]," ")-1),[Organization])

Going to see if MajP's suggestion applies to me. But not tonight.

 

[theDBguy]

Finally figured out that this will parse the first word of a string and not throw the error message.

Org: IIf(InStr([Organization]," "),Left([Organization],InStr([Organization]," ")-1),[Organization])

Going to see if MajP's suggestion applies to me. But not tonight.

Hi. Glad to hear you got it sorted out. Good luck with your project.