First word in string (1 Viewer)

Eljefegeneo

Still trying to learn
Local time
Today, 05:58
Joined
Jan 10, 2011
Messages
883
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:

Code:
 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.

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

theDBguy

I’m here to help
Staff member
Local time
Today, 05:58
Joined
Oct 29, 2018
Messages
18,742
Try adding an initial IIf() check to see if a space char exists.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:58
Joined
May 7, 2009
Messages
16,077
why not deliberately remove Leading/Trailing spaces:

Trim$([Organization])
 

Eljefegeneo

Still trying to learn
Local time
Today, 05:58
Joined
Jan 10, 2011
Messages
883
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

You've got your good things, and you've got mine.
Local time
Today, 08:58
Joined
May 21, 2018
Messages
6,259
 

Eljefegeneo

Still trying to learn
Local time
Today, 05:58
Joined
Jan 10, 2011
Messages
883
Finally figured out that this will parse the first word of a string and not throw the error message.
Code:
Org: IIf(InStr([Organization]," "),Left([Organization],InStr([Organization]," ")-1),[Organization])

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

theDBguy

I’m here to help
Staff member
Local time
Today, 05:58
Joined
Oct 29, 2018
Messages
18,742
Finally figured out that this will parse the first word of a string and not throw the error message.
Code:
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.
 

Users who are viewing this thread

Top Bottom