Can't nest InStr in a Left function

Why doesn't this expression work in an Access Query?

Postcode 1: Left(Trim([Clients].[Postcode]),InStr(1,[Clients].[Postcode]," ")-1)

I am getting an error that the expression is too complex.

If I put the InStr in a column of it's own and the Left with a constant for the length, they both work.

What I need to do is show the first part of the postcode.  In the UK we have postcodes (zipcodes) that have a space in the middle eg: NW1 4FS or B1 2GT or WC1Y 3CE.
Is there another way to parse the string?

Could it be data related?  There are some postcode fields that are empty or don't have a space
Laurence MartinTraining DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Postcode_1: Left(Trim([Clients].[Postcode]),InStr([Clients].[Postcode]," ")-1)

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Could it be data related?  There are some postcode fields that are empty or don't have a space>>


 You'd want:

 IIf(Trim(nz([Clients].[PostCode],""))="","", Left(Trim([Clients].[Postcode]), InStr(Trim([Clients].[Postcode])," ")-1))

even this is not perfect though.  It assumes that if a value is there that it contains a space.

Also make sure your app compiles without error.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Laurence MartinTraining DirectorAuthor Commented:
It seems that the whole query is too complex and this just pushes it over the edge.  It has a massive From clause with lots of outer joins.

If I create a second query on the main one and add the calculated field then it works.

However, Jim's solution is better and so I'll build the IIf too.

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Bill PrewIT / Software Engineering ConsultantCommented:
Try this, it should protect you from most problems with that field (blank, no embeded space, etc).

Postcode1:IIF(Trim([Clients].[Postcode])<>"",IIF(InStr(Trim([Clients].[Postcode])," ")>0,Left(Trim([Clients].[Postcode]),InStr(Trim([Clients].[Postcode])," ")-1),[Clients].[Postcode]),[Clients].[Postcode])

Open in new window

Dale FyeOwner, Dev-Soln LLCCommented:
This appends a space to the right of the [PostCode] to ensure that every record will have a value returned by the Instr( ) function.
I then take everything including to the left of that first space using the Left() function and then trim the spaces.
PostCode1:TRIM(Left([Clients].[PostCode] & " ", instr([Clients].[PostCode] & " ", " "))

Open in new window

This ensures that Trim will work, because there is no chance that a NULL will be processed.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
That's a great approach.  Get's rid of the IIF() to.

Dale FyeOwner, Dev-Soln LLCCommented:
Thanks, Jim.  I use this approach alot when using instr() or instrrev().  It generally takes some thought to get it right, but once I do, it generally works like a charm.

Bill PrewIT / Software Engineering ConsultantCommented:
I guess the only note on that clever approach is that NULL values become "empty strings" in the process.  That might be a good thing, or a bad thing, depending on the use case.

Dale FyeOwner, Dev-Soln LLCCommented:

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.