Find and extract text if it occurs in first 4 characters

[CuriousGeo]

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]

You had the right idea
This should work in a query expression

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

 

[MajP]

If it is not always 01 and not always 4 characters you can build something more generic.

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]

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

SELECT CompInfoJun212016.tCompany
, CompInfoJun212016.nSales
, CompInfoJun212016.tCountry
FROM CompInfoJun212016
WHERE (((Left([nsales],4)) Like "*30*"));

Good luck

 

[CuriousGeo]

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]

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.

 

[CuriousGeo]

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.

 

[MajP]

Just go into vba. Add a standard module, and paste. To get to vba the tab is "Database Tools" not "Developer" as it is in Excel.

https://www.youtube.com/watch?v=IIuKCxPlaTY