Avatar of Laurence Martin
Laurence Martin
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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
Microsoft AccessSQL

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
John Tsioumpris

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

Open in new window

ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Laurence Martin

ASKER
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.

Thanks
Bill Prew

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


»bp
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dale Fye

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 (EE MVE)

That's a great approach.  Get's rid of the IIF() to.

Jim.
Dale Fye

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.

Dale
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bill Prew

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.


»bp
Dale Fye

Bill,

True.