Help with an SQL SELECT statement in ASP page

I have built this SQL statement which should
create the RS I need.

strSQL1 = "SELECT [INVENTRY MASTER].BOX_NO FROM [INVENTRY MASTER] WHERE
Left([INVENTRY MASTER].BOX_NO, PatIndex('%821%', [INVENTRY MASTER].BOX_NO) -
1) NOT LIKE '%[1-9]%' AND [INVENTRY MASTER].BOX_NO LIKE '%821%';"

This Line:

objRS1.Open strSQL1, objConn

Causes this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter
passed to the substring function.

I have searched google, and found reference to the error meaning it found a
space in the first position. I tried adding LTRIM into my statement to cure
it but it made no difference, I may be barking up the wrong tree so to speak
;) but I cant find any other information on it.

If anyone has any ideas why this statement does not work I'd be very
grateful, the project has to be completed today, and this is the last thing
to get working now!

Ta

Paul McGuireThe length parameter in your LEFT(String, Length) function is

PatIndex('%821%', [INVENTRY MASTER].BOX_NO) - 1

PatIndex can return 0, if your string '821' isn't part of your BOX_NO, which makes the length = -1, which isn't allowed as a valid length. Your additional condition does not help, since the expression as a whole will be evaluated.

Consider to use a view to put your conditon
[INVENTRY MASTER].BOX_NO LIKE '%821%'|||Let me expand on what I am trying to achieve to see if you can help further.

imagine a table collumn

BOX_NO
--------
JHIS 0000821
JHIS 0000821a
JHIS 0000821b
JHIS 00821
JHIS 00821a
JHIS 0001821
JHIS 0001821a
JHIS 0001821b
JHIS 01821
JHIS 01821a

A user on the internet page I am writing will enter 821 because he wants a list of the boxes:

JHIS 0000821
JHIS 0000821a
JHIS 0000821b
JHIS 00821

The JHIS is the users account number the leading 0's can vary which is what has caused the need to use PatIndex in the way which I have? Is there another way?

All the user knows is box number JHIS 0000821 is box number 821 if he wanted JHIS 0001821 he would enter 1821 and expect to get these boxes returned:

JHIS 0001821
JHIS 0001821a
JHIS 0001821b
JHIS 01821
JHIS 01821a

Any ideas on the best way to do this? Am i on the right lines?|||Why not just

WHERE BOX_NO LIKE '%0821%'

?|||erm I am not sure :) I will put this in now... it seems like this may work if I just add a proceeding '0' to what has been inputted and do a LIKE

If this works I'm going to be very embarrised but pleased to because its been bugging me for hours!

I will let you know if this solves the issue|||Thank You very much! I obviously could not see the wood for the trees!

I changed it slightly because as it stood if 821 was entered 0008210 returned aswell which was not wanted.

by changing the last % to a [a-z] and adding an OR and just looking for %0821 I get back exactly what I need. And its a nice simple select statement!

So I offer you a big thanks and a :) this should keep the managment happy!|||You are welcome.sql