Hello,

This query returns an "Invalid Procedure Call" error because of the Len function:
SELECT DISTINCTROW tbl_Launch_L.CO_NUMBER, tbl_Launch_L.FRT_CHG_CD, tbl_Launch_L.Ship_Via, tbl_Launch_L.Ship_Via_Code
FROM tbl_Launch_L INNER JOIN tbl_ShippingCodes_L ON tbl_Launch_L.Ship_Via_Code = tbl_ShippingCodes_L.CODE
WHERE (((tbl_Launch_L.FRT_CHG_CD)='2') AND ((tbl_Launch_L.Ship_Via) Like '*:*') AND ((tbl_ShippingCodes_L.Type)='N') AND ((tbl_ShippingCodes_L.Required)='Y') AND ((IsNumeric(Right([SHIP_VIA],Len([SHIP_VIA])-4)))<>1))
ORDER BY tbl_Launch_L.CO_NUMBER;

This query works perfectly when I hard code the length:
SELECT DISTINCTROW tbl_Launch_L.CO_NUMBER, tbl_Launch_L.FRT_CHG_CD, tbl_Launch_L.Ship_Via, tbl_Launch_L.Ship_Via_Code
FROM tbl_Launch_L INNER JOIN tbl_ShippingCodes_L ON tbl_Launch_L.Ship_Via_Code = tbl_ShippingCodes_L.CODE
WHERE (((tbl_Launch_L.FRT_CHG_CD)='2') AND ((tbl_Launch_L.Ship_Via) Like '*:*') AND ((tbl_ShippingCodes_L.Type)='N') AND ((tbl_ShippingCodes_L.Required)='Y') AND ((IsNumeric(Right([SHIP_VIA],Len(13-4)))<>1))
ORDER BY tbl_Launch_L.CO_NUMBER;

There is no ship_via value in my table with a length of less than 13 - so it never returns a negative number. I'm stumped. Can anyone help??!!

Thanks,
D

Maybe a null value???

and I don't understand the idea of this.. you are reading the n(4) char of the string.. so if the string has 6 char you are out, and if the string has 5, you take it and compare it to 1... IT's that a correct logic?

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

Hi gbianchi,

Thanks for responding. I am still struggling with this.
There are no null values in the ship_via field.
As for the code - Our FedEx ship vias looks like this "FGR:332158321". The "FGR:" is the shipping "code" and the numbers are our customer's account number. I extract the account number by asking for the right 9 characters. This is the same as saying 13-4 or Len([SHIP_VIA])-4). I then test to make sure that the 9 characters are all numeric.

Thanks,
D

Ok, another idea, use the mid (or was substr in access?) function, starting at 4????

This will return string that start at position 4...

see if that fails...

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

Thanks - but I would still need to use the Len function within the Mid to determine how many characters are to the right of the 4th. Ex: mid(4,Len([ship_via])-4)