Simple list box

[law]

When I start unselecting in a list box, when I deselect the last one,
it produces the following error: Run time error 5, and this is
highlighted:

strSQL = strSQL & "WHERE tblProduct.Product IN (" & Left(strTemp,
Len(strTemp) - 2) & ");"

any ideas?

[law]

should be

strSQL = strSQL & "WHERE tblProduct.Product ID (" & Left(strTemp,
Len(strTemp) - 2) & ");"

[law]

law wrote:

should be

strSQL = strSQL & "WHERE tblProduct.Product ID IN(" & Left(strTemp,
Len(strTemp) - 2) & ");"

[law]

law wrote:

should be

strSQL = strSQL & "WHERE tblProduct.Product ID IN(" & Left(strTemp,
Len(strTemp) - 2) & ");"

[rquintal]

You are trying to remove two characters from a zero-length string. You
need to test if the strTemp variable is empty, if not, perform your
trim, if it is do something else.

[law]

Thanks for the response, but I'm not sure how to do this.

[law]

When I set it to -0 it appears to work. Is this safe?

[dkintheuk]

change the line you posted to something like...

if strTemp = "" then
'***do what you want here is strTemp is empty
else
strSQL = strSQL & "WHERE [tblProduct].[Product ID (" &
Left(strTemp, Len(strTemp) - 2) & ")];"
end if

you need to decide what to do if the string is empty... also note the
square brackets i've put in there as it looks like you are trying to
generate a field name on the fly and as such you've made it include
spaces so it needs square brackets round the whole thing...

if you meant to test the value in [tblProduct].[Product ID] then you
missed out an = sing such as...

strSQL = strSQL & "WHERE [tblProduct].[Product ID] = (" & Left(strTemp,
Len(strTemp) - 2) & ");"

as you can see the square brackets are still needed for the Product ID
field name...

does that help?

DK

[Terry Kreft]

What sbout if the string is "a" ?

You should test like this

if Len(strTemp) < 2 then
'***do what you want here is strTemp is empty
else
strSQL = strSQL & "WHERE [tblProduct].[Product ID (" & Left(strTemp,
Len(strTemp) - 2) & ")];"
end if
--
Terry Kreft
MVP Microsoft Access
"dkintheuk" <rm*******@firenet.uk.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

change the line you posted to something like...

if strTemp = "" then
'***do what you want here is strTemp is empty
else
strSQL = strSQL & "WHERE [tblProduct].[Product ID (" &
Left(strTemp, Len(strTemp) - 2) & ")];"
end if

you need to decide what to do if the string is empty... also note the
square brackets i've put in there as it looks like you are trying to
generate a field name on the fly and as such you've made it include
spaces so it needs square brackets round the whole thing...

if you meant to test the value in [tblProduct].[Product ID] then you
missed out an = sing such as...

strSQL = strSQL & "WHERE [tblProduct].[Product ID] = (" & Left(strTemp,
Len(strTemp) - 2) & ");"

as you can see the square brackets are still needed for the Product ID
field name...

does that help?

DK

[dkintheuk]

ah yes obvious when you say it like that...