Invalid Procedure Call (driving me nuts)

[joave]

Hi:

John Spencer's help has been invaluable so if you read this John, thank
you. I am getting closer, but I am not quite there with this string
manipulation issue. Here is the latest:

SELECT [Created By]
FROM PDFTable
WHERE Left$ ([Created By], InStr( [Created By] , "(" ) -2 ) = "Joe Smith";

This code gives the Invalid Procedure Call. I am trying this because my
Inner Join is missing some of the names and I am trying to troubleshoot (with
no success).

But, why is this invalid? I can substitute a number in place of the InStr
statement and it works fine. I can take out the -2 and it works fine (if I
add two additonal characters to the right side of the expression which is a
space and left parenthesis " ("). However, that is not practical for this
application.

Please note that I cannot change the field name because the raw data is set
that way.

Thank you,

Dave

 

[Ken Snell [MVP]]

That error will occur if a ( character is not found in the Created By field.
In that case, the InStr function will return a 0, and then your expression
subtracts 2. It's not possible for a Left$ function to return the
leftmost -2 characters of a string.

So, it's best to put an additional WHERE clause criterion in the query:

SELECT [Created By]
FROM PDFTable
WHERE InStr( [Created By] , "(" ) > 2 AND
Left$ ([Created By], InStr( [Created By] , "(" ) -2 ) = "Joe Smith";