Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-10-2019, 05:23 AM   #1
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 201
Thanks: 69
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Expressions to extract portion of a text string in Access

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
Thanks!

Hello1 is offline   Reply With Quote
Old 06-10-2019, 06:37 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,646
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Expressions to extract portion of a text string in Access

Hi. See if the Regular Expression article on my blog could help you with this.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-10-2019, 07:26 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,829
Thanks: 56
Thanked 1,032 Times in 998 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Expressions to extract portion of a text string in Access

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)

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-10-2019, 09:53 AM   #4
rpeare
Newly Registered User
 
Join Date: Sep 2016
Posts: 18
Thanks: 0
Thanked 3 Times in 2 Posts
rpeare is on a distinguished road
Re: Expressions to extract portion of a text string in Access

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
rpeare is offline   Reply With Quote
Old 06-10-2019, 10:45 AM   #5
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,560
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Expressions to extract portion of a text string in Access

Shouldn't that be ?
Code:
iif(instr([Fieldname], <SearchString>), mid([Fieldname], instr([Fieldname],<SearchString>), len(SearchString)), null)
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-10-2019, 11:22 AM   #6
rpeare
Newly Registered User
 
Join Date: Sep 2016
Posts: 18
Thanks: 0
Thanked 3 Times in 2 Posts
rpeare is on a distinguished road
Re: Expressions to extract portion of a text string in Access

yes, I botched the second instr statement
rpeare is offline   Reply With Quote
Old 06-10-2019, 11:39 AM   #7
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,560
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Expressions to extract portion of a text string in Access

Quote:
Originally Posted by rpeare View Post
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.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-11-2019, 06:56 AM   #8
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 201
Thanks: 69
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Re: Expressions to extract portion of a text string in Access

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)
Hello1 is offline   Reply With Quote
Old 06-11-2019, 04:11 PM   #9
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,717
Thanks: 105
Thanked 1,512 Times in 1,425 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Expressions to extract portion of a text string in Access

Quote:
Originally Posted by rpeare View Post
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 is offline   Reply With Quote
Old 06-11-2019, 04:17 PM   #10
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,717
Thanks: 105
Thanked 1,512 Times in 1,425 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Expressions to extract portion of a text string in Access

Quote:
Originally Posted by Hello1 View Post
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.

Galaxiom is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to extract portion of text yavahn Queries 8 12-02-2016 04:12 PM
Replacing a portion of a text string with a field value Falchthehunter Modules & VBA 2 11-27-2016 06:32 AM
Extract String from Text LadyDi Excel 2 09-28-2012 04:33 AM
Extract date portion from timestamp JJtwin Crystal Reports 1 12-11-2009 08:12 PM
Extract 6 digit text string only? JimiH Modules & VBA 13 02-12-2004 02:13 AM




All times are GMT -8. The time now is 05:00 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World