Find and extract text if it occurs in first 4 characters (1 Viewer)

CuriousGeo

Registered User.
Local time
Today, 17:39
Joined
Oct 15, 2012
Messages
59
Hello, I've tried searching for this problem in this forum, but no luck. In a field I have a text string (combination of alpha/numeric)
What I'm trying to extract is the string if it occurs within the first 4 characters of the string. I probably need to use a combination of Left function and Instr function?

example

look for "01" in a field in 1st four characters

012234 output = 01
PC012234 output= 01
AB220122 output= nothing/ignore
 

isladogs

MVP / VIP
Local time
Today, 21:39
Joined
Jan 14, 2017
Messages
18,186
You had the right idea
This should work in a query expression

Code:
Expr1: IIf(InStr(Left([fieldname],4),"01")>0,"01","")
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:39
Joined
May 21, 2018
Messages
8,463
If it is not always 01 and not always 4 characters you can build something more generic.

Code:
Public Function GetString(ByVal SearchFor As String, ByVal SearchIn As String, Optional LengthToSearch As Integer = 4) As String
  SearchIn = Left(SearchIn, LengthToSearch)
  If InStr(SearchIn, SearchFor) > 0 Then
     GetString = SearchFor
  Else
     GetString = "Nothing/Ignore"  'or if you really mean to ignore then get rid of else part
  End If
End Function

I was assuming you might have two fields one with the string to search for and one with the search in

Select GetString([field1],[field2],6) as someName from someTable
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:39
Joined
Jan 23, 2006
Messages
15,364
This is SQL for a query to get records where leftmost 4 chars contain 30.
You can adapt to your field and table names and replace 30 with 01
Code:
SELECT CompInfoJun212016.tCompany
, CompInfoJun212016.nSales
, CompInfoJun212016.tCountry
FROM CompInfoJun212016
WHERE (((Left([nsales],4)) Like "*30*"));

Good luck
 

CuriousGeo

Registered User.
Local time
Today, 17:39
Joined
Oct 15, 2012
Messages
59
Thank you isladogs!
I didn't know to use IIf and reverse the order of the functions them. I'm fairly inexperienced with functions.
 

isladogs

MVP / VIP
Local time
Today, 21:39
Joined
Jan 14, 2017
Messages
18,186
You're welcome. The way to solve this type of thing is just to build up step by step in a query. Start with the Left part to grab the string to be searched. Add the InStr to search it. Finish with the IIf to set the output. Test after each step

Do look at the other two solutions by MajP and jdraw in case either suits your needs better.
 
Last edited:

CuriousGeo

Registered User.
Local time
Today, 17:39
Joined
Oct 15, 2012
Messages
59
Also thank you to MajP and jdraw. MajP that looks as if it works, but beyond my ability of creating a public function, but I will give it a try.

jdraw, I tried your way and it works too. Especially since it just shows the fields with the value I want rather than putting the value in another field like my initial question asked.
 

Users who are viewing this thread

Top Bottom