Can't nest InStr in a Left function

Laurence Martin
Laurence Martin used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

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

Open in new window

President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<Could it be data related?  There are some postcode fields that are empty or don't have a space>>

 Yes.

 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.

Jim.
Laurence MartinTraining Director

Author

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.

Thanks
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
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
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

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

Jim.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
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 FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Bill,

True.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial