Expressions to extract portion of a text string in Access (1 Viewer)

Hello1

Registered User.
Local time
Today, 17:57
Joined
May 17, 2015
Messages
271
Hello,

Im trying to extract some text from a text field.
For example if the value is " John" I need the expression to return me "John" or if the value is " John co. sth" I also need it to return me "John".
I tried this:
Code:
IIf(InStr(InStr(" John co. sth"," ")+1," John co. sth"," ") _
<>0, Right(" John ",Len(" John co. sth")-InStr(InStr(" John co. sth"," ")+1, _
" John co. sth"," ")),Right(" John co. sth",Len(" John co. sth")-InStr(" John co. sth"," ")))

For " John" it returns the right result, but for " John co. sth" I get "co. sth".

Another thing I need is:
For "John" to return same "John" and for "John co. sth" to return also "John".

If I cant make one expression for all of this Im going to do it in VBA probably anyway so I would say something like if the first character is " " then the expression for the first example I gave, if not then for the second example.

I hope I have been clear enough :confused:
Thanks!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:57
Joined
Sep 12, 2006
Messages
15,613
result = Split(Trim(expn), " ")(0)

this removes any leading/trailing spaces, and then return the first word in the string (splitting the full string on space characters)
 

rpeare

Registered User.
Local time
Today, 08:57
Joined
Sep 15, 2016
Messages
18
Code:
iif(instr([Fieldname], <SearchString>), mid([Fieldname], instr([Fieldname]), len([Fieldname]), null)

would return the search string if exists in the field, otherwise return a null value
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:57
Joined
Sep 21, 2011
Messages
14,044
Shouldn't that be ?
Code:
iif(instr([Fieldname], <SearchString>), mid([Fieldname], instr([Fieldname],<SearchString>), len(SearchString)), null)
 

rpeare

Registered User.
Local time
Today, 08:57
Joined
Sep 15, 2016
Messages
18
yes, I botched the second instr statement
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:57
Joined
Sep 21, 2011
Messages
14,044
yes, I botched the second instr statement

Well initially I thought just the length parameter of the function was referring to the incorrect variable, but as I was altering it, I noticed the second parameter of the second Instr was missing.;)
 

Hello1

Registered User.
Local time
Today, 17:57
Joined
May 17, 2015
Messages
271
Thanks guys. For the first case I added just Trim to get rid of that extra space on begging and for the second one:
Code:
Left([FieldName], InStr(1, [FieldName] & " ", " ") - 1)
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:57
Joined
Jan 20, 2009
Messages
12,849
Code:
iif(instr([Fieldname], <SearchString>), mid([Fieldname], instr([Fieldname]), len([Fieldname]), null)
would return the search string if exists in the field, otherwise return a null value
Another variation is to concatenate the search string to the end of the expression that is being searched in case it isn't contained in the searched expression.

I use this where there is an optional dot followed by more characters at the end of the expression and I don't want the dot and subsequent characters.

The resulting expression is simpler than including two different versions of the search for inputs with and without the dot.

For example:
Code:
Left([fieldname], Instr([fieldname] & ".", ".") - 1)

rather than:
Code:
Iif(Instr([fieldname],"."), Left([fieldname], Instr([fieldname],".") - 1),[fieldname])
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:57
Joined
Jan 20, 2009
Messages
12,849
Thanks guys. For the first case I added just Trim to get rid of that extra space on begging and for the second one:
Code:
Left([FieldName], InStr(1, [FieldName] & " ", " ") - 1)

BTW The first parameter of InStr() is optional.

AFAIK it is the only function where an optional parameter is not after all required parameters.

I have occasionally contemplated how the function definition is built and never worked it out. I don't think user defined functions can be written like this at all.
 

Users who are viewing this thread

Top Bottom