MS Access Query Invalid Procedure Call InStr([xxx],"y")-1

Gary Croxford
Gary Croxford used Ask the Experts™
on
Thank you for looking at my question,

I am querying a table in access that has a text field containing data strings of varying lengths.
I am trying to separate out the first part of the string - nominally the first 16 to 19 characters followed by a semi-colon.

To achieve this I have tried the following:

ItemName: Left([Description],InStr([Description,":")-1)
and
ItemName: Left([Description],(InStr([Description,":")-1))

but both return an invalid procedure call message.

ItemName: Left([Description], InStr([Description,":"))
returns the string I need and the semi-colon

What have I missed?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It is possible that in your database to exists either records without semicolon or with Null values. When you run
ItemName: Left([Description], InStr([Description,":"))
for that kind if items InStr will return 0 and Left function works OK

When you run
ItemName: Left([Description],InStr([Description,":")-1)
then Left function will execute an Left with -1 as parameter and this is not OK

In order to find a solution you should define what to do with these items. A possible solution could be

ItemName: Left([Description],IIF(InStr([Description,":")-1<0, 0, InStr([Description,":")-1))
Gary CroxfordOperations Support Analyst

Author

Commented:
Thank you for your help

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