Invalid Procedure Call problem

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Access97
I have a query in design view with the following in the Field property:

WhereIs: Left([CurrentLocation],InStr([CurrentLocation],"-")-2)

In the underlying table [CurrentLocation] is a Text Field that contains a
primary and secondary location separated by space-hyphen-space. I need to
extract only the primary location.

The above works fine without the -2, but shows the space-hyphen at the end
of the result.

Where am I going wrong here?

TIA

WSF
 
I should add that having )+2) in the equation produces the two extra (= 4
unwanted) characters, but the minus (-) doesn't compute!

WSF
 
A very rapid response and before I could further qualify my problem.

Perhaps I should have explained.
If [CurrentLocation] returns say "Primary Location - Secondary Location"
then
Left([CurrentLocation],InStr([CurrentLocation],"-") returns "Primary
Location -"
So how do I get rid of the space-hyphen? to return just "Primary Location" ?
I have mention in my subsequent post you can add to the equation using the
"+" operator but not deduct using the "-" ??

WSF





PC Datasheet said:
Not using the -2!!!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


WSF said:
Access97
I have a query in design view with the following in the Field property:

WhereIs: Left([CurrentLocation],InStr([CurrentLocation],"-")-2)

In the underlying table [CurrentLocation] is a Text Field that contains a
primary and secondary location separated by space-hyphen-space. I need to
extract only the primary location.

The above works fine without the -2, but shows the space-hyphen at the end
of the result.

Where am I going wrong here?

TIA

WSF
Click to expand...
Click to expand...
 
What happens when you try the expression that you posted (the one with
the -2 in it)? That should work.
 
I get the "Invalid Procedure Call problem" message.
As per my other response to PC Datasheet

If [CurrentLocation] returns say "Primary Location - Secondary Location"
then
Left([CurrentLocation],InStr([CurrentLocation],"-") returns "Primary
Location -"
So how do I get rid of the space-hyphen? to return just "Primary Location" ?
I have mention in my subsequent post you can add to the equation using the
"+" operator but not deduct using the "-" ??

The query works without the )-2 part, but errors with it.
Ironically if I substutute the - for a + it adds the extra two characters
(which obviously I don't want)

Got me stuffed!

WSF




Ken Snell said:
What happens when you try the expression that you posted (the one with
the -2 in it)? That should work.

--
Ken Snell
<MS ACCESS MVP>

WSF said:
Access97
I have a query in design view with the following in the Field property:

WhereIs: Left([CurrentLocation],InStr([CurrentLocation],"-")-2)

In the underlying table [CurrentLocation] is a Text Field that contains a
primary and secondary location separated by space-hyphen-space. I need to
extract only the primary location.

The above works fine without the -2, but shows the space-hyphen at the end
of the result.

Where am I going wrong here?

TIA

WSF
Click to expand...
Click to expand...
 
Are you sure that none of your CurrentLocation values do not have a "-"
character in them? I'm guessing that at least one value doesn't, and the
Left function is erroring because you're trying to do this:
Left([CurrentLocation], -2)


--
Ken Snell
<MS ACCESS MVP>

WSF said:
I get the "Invalid Procedure Call problem" message.
As per my other response to PC Datasheet

If [CurrentLocation] returns say "Primary Location - Secondary Location"
then
Left([CurrentLocation],InStr([CurrentLocation],"-") returns "Primary
Location -"
So how do I get rid of the space-hyphen? to return just "Primary Location" ?
I have mention in my subsequent post you can add to the equation using the
"+" operator but not deduct using the "-" ??

The query works without the )-2 part, but errors with it.
Ironically if I substutute the - for a + it adds the extra two characters
(which obviously I don't want)

Got me stuffed!

WSF




Ken Snell said:
What happens when you try the expression that you posted (the one with
the -2 in it)? That should work.

--
Ken Snell
<MS ACCESS MVP>

WSF said:
Access97
I have a query in design view with the following in the Field property:

WhereIs: Left([CurrentLocation],InStr([CurrentLocation],"-")-2)

In the underlying table [CurrentLocation] is a Text Field that
Click to expand...
Click to expand...
contains
a
Click to expand...
Click to expand...
 
Mr. Snell,
You are a gentleman and a scholar!
And 100% correct.
Thank you very much - problem solved.

Regards,
Bill Fraser


Ken Snell said:
Are you sure that none of your CurrentLocation values do not have a "-"
character in them? I'm guessing that at least one value doesn't, and the
Left function is erroring because you're trying to do this:
Left([CurrentLocation], -2)


--
Ken Snell
<MS ACCESS MVP>

WSF said:
I get the "Invalid Procedure Call problem" message.
As per my other response to PC Datasheet

If [CurrentLocation] returns say "Primary Location - Secondary Location"
then
Left([CurrentLocation],InStr([CurrentLocation],"-") returns "Primary
Location -"
So how do I get rid of the space-hyphen? to return just "Primary
Click to expand...
Location"
?
I have mention in my subsequent post you can add to the equation using the
"+" operator but not deduct using the "-" ??

The query works without the )-2 part, but errors with it.
Ironically if I substutute the - for a + it adds the extra two characters
(which obviously I don't want)

Got me stuffed!

WSF




Ken Snell said:
What happens when you try the expression that you posted (the one with
the -2 in it)? That should work.

--
Ken Snell
<MS ACCESS MVP>

Access97
I have a query in design view with the following in the Field property:

WhereIs: Left([CurrentLocation],InStr([CurrentLocation],"-")-2)

In the underlying table [CurrentLocation] is a Text Field that
Click to expand...
contains
a
primary and secondary location separated by space-hyphen-space. I
Click to expand...
need
to
extract only the primary location.

The above works fine without the -2, but shows the space-hyphen at
Click to expand...
the
end
of the result.

Where am I going wrong here?

TIA

WSF
Click to expand...
Click to expand...
Click to expand...
 
That's the second time this week I've been called those things! < g >

You're welcome.

--
Ken Snell
<MS ACCESS MVP>

WSF said:
Mr. Snell,
You are a gentleman and a scholar!
And 100% correct.
Thank you very much - problem solved.

Regards,
Bill Fraser


Ken Snell said:
Are you sure that none of your CurrentLocation values do not have a "-"
character in them? I'm guessing that at least one value doesn't, and the
Left function is erroring because you're trying to do this:
Left([CurrentLocation], -2)
Click to expand...
Click to expand...
 
Back
Top