Invalid procedure call error

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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.
 
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.
 
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.
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top