Invalid procedure call error

G

Guest

The following expression works.

Left([Clients]![Name],InStr(1,[Clients]![Name],","))

If I add "-1" after the instr function, however, Access would return the
"Invalid procedure call " message.

Left([Clients]![Name],InStr(1,[Clients]![Name],",")-1)

A separate function, InStr(1,[Clients]![Name],",")-1, works fine.

What am I doing wrong? Thanks in advance for your help.
 
D

Douglas J Steele

I'm guessing this only happens when it encounters a name that doesn't have a
comma in it.

InStr(1,[Clients]![Name],",") is looking for the first comma in
[Clients]![Name]. If it doesn't find a comma, it's going to return 0.

Left won't work if -1 is passed to it.
 
G

Guest

It does not work. I believe the fourth argument in the INSTR function is for
comparison.

I am looking to reduce the value return by INSTR by one so that I can get
the last name without the ",". The combined function of Find and LEFT
functions in Excel works fine but not in Access.
 
G

Guest

No. It is not the case. The names have "," and the results have last names
followed by "," because I could not reduce the value returned by INSTR by one.
 
G

Guest

oh, I see. I think you have the -1 in the wrong place. Try:

Left([Clients]![Name],InStr(1,[Clients]![Name],",",))-1


Glazunov said:
It does not work. I believe the fourth argument in the INSTR function is for
comparison.

I am looking to reduce the value return by INSTR by one so that I can get
the last name without the ",". The combined function of Find and LEFT
functions in Excel works fine but not in Access.

--
Thanks.

CY


Glazunov said:
The following expression works.

Left([Clients]![Name],InStr(1,[Clients]![Name],","))

If I add "-1" after the instr function, however, Access would return the
"Invalid procedure call " message.

Left([Clients]![Name],InStr(1,[Clients]![Name],",")-1)

A separate function, InStr(1,[Clients]![Name],",")-1, works fine.

What am I doing wrong? Thanks in advance for your help.
Click to expand...
Click to expand...
 
G

Guest

Your expression has a mismatch.

You cannot subtract a value (i.e. 1) from a string function (i.e. LEFT).

--
Thanks.

CY


xRoachx said:
oh, I see. I think you have the -1 in the wrong place. Try:

Left([Clients]![Name],InStr(1,[Clients]![Name],",",))-1


Glazunov said:
It does not work. I believe the fourth argument in the INSTR function is for
comparison.

I am looking to reduce the value return by INSTR by one so that I can get
the last name without the ",". The combined function of Find and LEFT
functions in Excel works fine but not in Access.

--
Thanks.

CY


Glazunov said:
The following expression works.

Left([Clients]![Name],InStr(1,[Clients]![Name],","))

If I add "-1" after the instr function, however, Access would return the
"Invalid procedure call " message.

Left([Clients]![Name],InStr(1,[Clients]![Name],",")-1)

A separate function, InStr(1,[Clients]![Name],",")-1, works fine.

What am I doing wrong? Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

LOL! Thx for pointing out the obvious for me again...sorry about that, too
little sleep last night. I tested this and it worked:

Left([Clients]![Name],(InStr(1,[Clients]![Name],","))-1)

Let me know if this finally did the trick. I tested it with this format:

myName = Left(myName, (InStr(1, myName, ",")) - 1)

Glazunov said:
Your expression has a mismatch.

You cannot subtract a value (i.e. 1) from a string function (i.e. LEFT).

--
Thanks.

CY


xRoachx said:
oh, I see. I think you have the -1 in the wrong place. Try:

Left([Clients]![Name],InStr(1,[Clients]![Name],",",))-1


Glazunov said:
It does not work. I believe the fourth argument in the INSTR function is for
comparison.

I am looking to reduce the value return by INSTR by one so that I can get
the last name without the ",". The combined function of Find and LEFT
functions in Excel works fine but not in Access.

--
Thanks.

CY


:

The following expression works.

Left([Clients]![Name],InStr(1,[Clients]![Name],","))

If I add "-1" after the instr function, however, Access would return the
"Invalid procedure call " message.

Left([Clients]![Name],InStr(1,[Clients]![Name],",")-1)

A separate function, InStr(1,[Clients]![Name],",")-1, works fine.

What am I doing wrong? Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It does not work for me. Your expression is the same as mine.

Perhaps I should point out that I am using the expression in a simple query
of an existing table.

I am not using the expression in VBA.

Please refer to my original question for the expressions I tried. It is a
mystery to me why it does not work. Thanks.


--
Thanks.

CY


xRoachx said:
LOL! Thx for pointing out the obvious for me again...sorry about that, too
little sleep last night. I tested this and it worked:

Left([Clients]![Name],(InStr(1,[Clients]![Name],","))-1)

Let me know if this finally did the trick. I tested it with this format:

myName = Left(myName, (InStr(1, myName, ",")) - 1)

Glazunov said:
Your expression has a mismatch.

You cannot subtract a value (i.e. 1) from a string function (i.e. LEFT).

--
Thanks.

CY


xRoachx said:
oh, I see. I think you have the -1 in the wrong place. Try:

Left([Clients]![Name],InStr(1,[Clients]![Name],",",))-1


:

It does not work. I believe the fourth argument in the INSTR function is for
comparison.

I am looking to reduce the value return by INSTR by one so that I can get
the last name without the ",". The combined function of Find and LEFT
functions in Excel works fine but not in Access.

--
Thanks.

CY


:

The following expression works.

Left([Clients]![Name],InStr(1,[Clients]![Name],","))

If I add "-1" after the instr function, however, Access would return the
"Invalid procedure call " message.

Left([Clients]![Name],InStr(1,[Clients]![Name],",")-1)

A separate function, InStr(1,[Clients]![Name],",")-1, works fine.

What am I doing wrong? Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

No. It is not the case. The names have "," and the results have last names
followed by "," because I could not reduce the value returned by INSTR by
one.

--
Thanks.

CY


Douglas J Steele said:
I'm guessing this only happens when it encounters a name that doesn't have a
comma in it.

InStr(1,[Clients]![Name],",") is looking for the first comma in
[Clients]![Name]. If it doesn't find a comma, it's going to return 0.

Left won't work if -1 is passed to it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Glazunov said:
The following expression works.

Left([Clients]![Name],InStr(1,[Clients]![Name],","))

If I add "-1" after the instr function, however, Access would return the
"Invalid procedure call " message.

Left([Clients]![Name],InStr(1,[Clients]![Name],",")-1)

A separate function, InStr(1,[Clients]![Name],",")-1, works fine.

What am I doing wrong? Thanks in advance for your help.
Click to expand...
Click to expand...
 
G

Guest

Looking at your original post, the syntax appears the same but I have an
extra set of parenthesis (around InStr). I also tested the syntax in the
query builder and it worked:

WHAT: Left([testtab]![Test],(InStr(1,[testtab]![Test],","))-1)

Glazunov said:
It does not work for me. Your expression is the same as mine.

Perhaps I should point out that I am using the expression in a simple query
of an existing table.

I am not using the expression in VBA.

Please refer to my original question for the expressions I tried. It is a
mystery to me why it does not work. Thanks.


--
Thanks.

CY


xRoachx said:
LOL! Thx for pointing out the obvious for me again...sorry about that, too
little sleep last night. I tested this and it worked:

Left([Clients]![Name],(InStr(1,[Clients]![Name],","))-1)

Let me know if this finally did the trick. I tested it with this format:

myName = Left(myName, (InStr(1, myName, ",")) - 1)

Glazunov said:
Your expression has a mismatch.

You cannot subtract a value (i.e. 1) from a string function (i.e. LEFT).

--
Thanks.

CY


:

oh, I see. I think you have the -1 in the wrong place. Try:

Left([Clients]![Name],InStr(1,[Clients]![Name],",",))-1


:

It does not work. I believe the fourth argument in the INSTR function is for
comparison.

I am looking to reduce the value return by INSTR by one so that I can get
the last name without the ",". The combined function of Find and LEFT
functions in Excel works fine but not in Access.

--
Thanks.

CY


:

The following expression works.

Left([Clients]![Name],InStr(1,[Clients]![Name],","))

If I add "-1" after the instr function, however, Access would return the
"Invalid procedure call " message.

Left([Clients]![Name],InStr(1,[Clients]![Name],",")-1)

A separate function, InStr(1,[Clients]![Name],",")-1, works fine.

What am I doing wrong? Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I tried but it didn't work. The following is a copy of the formula that
causes the error. Any idea?

Expr1:
Left([Clients]![OrganizationName],(InStr(1,[Clients]![OrganizationName],","))-1)

I tried various things and nothing worked. Even the following did not return
an error -

Expr1:
Left([Clients]![OrganizationName],(InStr(1,[Clients]![OrganizationName],","))-],(InStr(1,[Clients]![OrganizationName],",")))

It simply return blanks.

(InStr(1,[Clients]![OrganizationName],","))-],(InStr(1,[Clients]![OrganizationName],",")) returns blanks.

(InStr(1,[Clients]![OrganizationName],","))-1 embedded in the LEFT function
returns an "Invalid procedure call".

Thanks.


--
Thanks.

CY


Glazunov said:
No. It is not the case. The names have "," and the results have last names
followed by "," because I could not reduce the value returned by INSTR by one.
--
Thanks.

CY


Glazunov said:
The following expression works.

Left([Clients]![Name],InStr(1,[Clients]![Name],","))

If I add "-1" after the instr function, however, Access would return the
"Invalid procedure call " message.

Left([Clients]![Name],InStr(1,[Clients]![Name],",")-1)

A separate function, InStr(1,[Clients]![Name],",")-1, works fine.

What am I doing wrong? Thanks in advance for your help.
Click to expand...
Click to expand...
 
G

Guest

Wow, I'm at a loss now. Are you sure it's not your data that's causing the
error?

Sorry I was not able to solve the issue, but if you figure it out, please
post back b/c I would like to know what it was. :)

Glazunov said:
I tried but it didn't work. The following is a copy of the formula that
causes the error. Any idea?

Expr1:
Left([Clients]![OrganizationName],(InStr(1,[Clients]![OrganizationName],","))-1)

I tried various things and nothing worked. Even the following did not return
an error -

Expr1:
Left([Clients]![OrganizationName],(InStr(1,[Clients]![OrganizationName],","))-],(InStr(1,[Clients]![OrganizationName],",")))

It simply return blanks.

(InStr(1,[Clients]![OrganizationName],","))-],(InStr(1,[Clients]![OrganizationName],",")) returns blanks.

(InStr(1,[Clients]![OrganizationName],","))-1 embedded in the LEFT function
returns an "Invalid procedure call".

Thanks.


--
Thanks.

CY


Glazunov said:
No. It is not the case. The names have "," and the results have last names
followed by "," because I could not reduce the value returned by INSTR by one.
--
Thanks.

CY


Glazunov said:
The following expression works.

Left([Clients]![Name],InStr(1,[Clients]![Name],","))

If I add "-1" after the instr function, however, Access would return the
"Invalid procedure call " message.

Left([Clients]![Name],InStr(1,[Clients]![Name],",")-1)

A separate function, InStr(1,[Clients]![Name],",")-1, works fine.

What am I doing wrong? Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Partial text returned from a query 0
Invalid Procedure Call Message 1
How to extract portion of text 5
Problems with a query; result to textbox. 2
Help with Module 3
Invalid Procedure Call 3
Two Different Characters in String 1
'Data type mismatch error' using Mid function in a query 3

Top