How do I pass param to an 'IN' clause?

[trygvea]

Here is my SQL statement.

DELETE *
FROM TableA
WHERE PKEY IN ([paramIDs])

When I am prompted for the param I enter id numbers separated by comma, ie 1,2. This will not work. I know this is probably a simple fix.

Please help.

 

[scriverb]

Try this code: The parameter prompt is quite long but we need the user to understand the format that is necessary when entering the ID's. It should be each ID bracketed by a comma including the first and last ID:

DELETE *
FROM TableA
WHERE Instr(1,[Enter paramIDs Format(,x,xx,xxx,x,x,)],PKEY) > 0;

Post back with any questions or comments.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[trygvea]

I tested your solution above but it does not work correctly. Here is my sample table.

PKey Description
3 Sample one.
8 Sample two.
38 Sample thirty eight.

If I use your above query and pass the parameter (,38,) then ALL three rows are deleted.

Thanks in advance for any other possible solutions.

 

[scriverb]

Sorry about that. Use this code update:

DELETE *
FROM TableA
WHERE Instr(1,[Enter paramIDs Format(,x,xx,xxx,x,x,)],"," & PKEY & ",&quot > 0;

Post back with your results.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[trygvea]

This example appears to be working but I'm not quiet sure I understand why it is working. Could you please take a minute to explain. When I researched the Instr function it suggested that it will return the index location of the string being search when it finds the match. If that is the case I don't understand how the query knows which rows to delete. And I also don't understand the > 0 peice.

Thanks in advance.

 

[scriverb]

Okay, we have created a string of possibile matches each bracketed by a comma(,). The reason for this is we are going to bracket the value in the field with commas so that we don't mistakenly find a match when we are looking for a 3 and find a 31. We are going to look for a ,3, which will not match with a ,31,. The WHERE clause expression is saying that we want to select each record where the Instr function returns a value greater than 0. This means that the PKEY value was found in the target string somewhere. You see it will return a position value of the beginning of the PKEY value in the target string. If it is 4 then you should be able to find ,3, starting the fourth(4) character from the left. This means that the records PKEY value is won we should select.

I hope this wasn't too confusing for you. I use this technique in many ways to substitute for an (If (A = 1) OR (A=10) OR (A=23) then) -- If Instr(1,",1,10,23,),"," & A & ",&quot >0 then

Post back if you have further questions.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[trygvea]

Thanks for taking the time to explain. However, I have found another problem.

If I am in Access and I run the query using the following parameters (,2,1,) both rows will be deleted. But when I pass the the same parameter string from an ASP page the query will only delete the Pkey of 2 and will leave PKey 1.

I am unable to sort this param string prior to passing it to the query. I'm about to give up and just call a FOR loop deleting the rows one at a time. I was trying to avoid repeat trips to the db.

It just seems like there should be a way to pass a string param to an IN clause in the SQL. If you have any additional comments I'm ready to listen. If not I understand.

Thanks for your time.