Invalid Procedure Call

[Emma Aumack]

Hi All,

I've been trying to connect two queries. In Qry_BPVSHADASP I am parsing out
a text field (ITEM) that has data as follows:

F5006 IMPRA FLEX 6 MM X 50 CM

and I am using an expression to do so as follows:

PCode: Left([Item],InStr([item]," ")-1)

In my other query Qry_MktgCNTApprvd, I have a field that has the field
Product_Code that matches the PCODE field in Qry_BPVSHADASP. When I try to
connect the two queries as follows:

PCODE <------> Product_Code

I get the error, "Invalid Procedure Call".

Can someone help me here? What am I doing wrong?

Thank you!!

 

[Lord Kelvan]

mmm i ran a test with what info you gave and i got no problem try
posting to us both of your queries it may not be that where the
problem resides

 

[louisjohnphillips]

mmm i ran a test with what info you gave and i got no problem try
posting to us both of your queries it may not be that where the
problem resides

Somewhere in the data there is an Item that contains no spaces. This
may be because the value is all non-space characters or because it is
null.

The invalid procedure call comes because the Left function expects a
positive value as its second argument.

If there is no space character, the second argument evaluates to -1.
If the space character is in the first postition, the second argument
evaluates to 0. Both cause the Left function to fail with this error.

-1 = Left([Item],InStr([item]," ")-1)

 

[Lord Kelvan]

thats a good point if that is the case he need to put something as the
criteria of his query

where InStr([item]," ") <> 0

but then that raises the question in this case it is impossible to get
thoes items that have no spaces

i am presuming that the format of the pcode is not always 5 characters
which is why you are using the instr function

 

[Duane Hookom]

PCode: Left([Item],InStr([item] & " "," ")-1)

 

[Emma Aumack]

Yes, the data I am extracting should be between 5 and 9 characters long.
There are 1341 records and I did not see any with only spaces or null.

 

[Emma Aumack]

Thank you all.

Duane, your solution worked. As always, you are the KING!!!


--
www.bardpv.com
Tempe, Arizona

PCode: Left([Item],InStr([item] & " "," ")-1)
--
Duane Hookom
Microsoft Access MVP

thats a good point if that is the case he need to put something as the
criteria of his query

where InStr([item]," ") <> 0

but then that raises the question in this case it is impossible to get
thoes items that have no spaces

i am presuming that the format of the pcode is not always 5 characters
which is why you are using the instr function