array to sql

[catchup]

I have a form.asp that has check boxes i post this fom to results.asp How in results.asp can i create an array of the request.form variables that have been submitted with a value, and create a sql to pull records from the db.

form.asp:
checkboxes names/value
a1/1
a2/2
a3/4
a4/4
a5/5
a6/6
a7/7
a8/8
a9/9
a10/10
a11/11
a12/12
a13/13
a14/14

Access DB
column name/value
bc/British Columbia
ab/Alberta
sk/Saskatchewan
mb/Manitoba
on/Ontario
pq/Quebec
ns/Nova Scotia
nb/New Brunswick
nfl/Newfoundland
pei/Prince Edward Island
yk/Yukon
nwt/Northwest Territories
nu/Nunavut

How the two data sets are joined
-form.asp if a1 selected display records on results.asp where bc has a value
-form.asp if a2 selected display records on results.asp where ab has a value
-form.asp if a3 selected display records on results.asp where sk has a value
-form.asp if a4 selected display records on results.asp where mb has a value
-form.asp if a5 selected display records on results.asp where on has a value
-form.asp if a6 selected display records on results.asp where pq has a value
-form.asp if a7 selected display records on results.asp where ns has a value
-form.asp if a8 selected display records on results.asp where nb has a value
-form.asp if a9 selected display records on results.asp where nfl has a value
-form.asp if a10 selected display records on results.asp where pei has a value
-form.asp if a11 selected display records on results.asp where yk has a value
-form.asp if a12 selected display records on results.asp where nwt has a value
-form.asp if a13 selected display records on results.asp where nu has a value
-form.asp if a14 selected display allrecords on results.asp

Things need to be done in a way so that what ever is selected on form.asp (1 selection or multiple selections), results.asp has a sql that pull that the records. so i assume its a dynamic sql formed by the form.asp values AND the records where there is value in the corellating form.asp to column name.

I hope that makes sense. Thanks. I have it done so that i have a bunch of if statements with correlating sql statements and with html to display the data formated to the page. So far my results.asp page is huge and hard to work with, with so many if statements to satify every condition, and so much html segments.
Thanks again!

[catchup]

Hi Dave I got things working, the above had a couple of issues, thanks for the start!

I'm a little lost though, by the form.asp selections this sql was created:
sql = Select * From Results Where [key] = 'bc' Or [key] = 'sk' Or [key] = 'ns' ORDER BY RND(ID);

What is and how does [key] work?

I thought that key maybe what an array value should be so i did that below. now my sql looks like this:

sql = Select * From Results Where (3,5,6,7,8,9) = 'sk' Or (3,5,6,7,8,9) = 'on' Or (3,5,6,7,8,9) = 'pq' Or (3,5,6,7,8,9) = 'ns' Or (3,5,6,7,8,9) = 'nb' Or (3,5,6,7,8,9) = 'nfl' ORDER BY RND(ID);

for things to work correctly,
maybe the = should be replaced with IN? it makes sense to have the sql say something like select * from results where bc IN " & myArray & " AND/OR ab IN " & myArray & " AND/OR sk IN " & myArray & " AND/OR...... I'm using AND/OR to demonstrate that i need to cover every possible condition of having just a request for a record with an ab value or just a bc value or for bc and ab and sk value.

really and honestly i feel like i'm trying to capture king cobra snakes in a pitch black room. would appreciate some direction. thanks.



<%
myArray="("
for each objItem in request.Form()
if left(objItem,1)="a" and request.Form(objItem) <> "" then
myArray=myArray & request.Form(objItem) & ","
end if
next
theLen=len(myArray)
myArray=left(myArray,theLen-1)
myArray=myArray & ")"
response.Write("myArray = " & myArray & "<br>")


Dim ary, sql, i
ary = Array("", "bc", "ab", "sk", "mb", "on", "pq", "ns", "nb", "nfl", "pei", "yk", "nwt", "nu")
sql = "Select * From Results"
lsql = Len(sql)
If Request.Form("a14") = "" Then
For i = 1 to 13
If Request.Form("a" & i) <> "" Then
If Len(sql) = lsql Then
sql = sql & " Where "
Else
sql = sql & " Or "
End If
sql = sql & (" " & myArray & " = '" & ary(i) & "'")
End If
Next
End If
sql = sql & " ORDER BY RND(ID);"
Response.Write "<p>sql = " & sql & "</p>" & vbCrLf

%>

[catchup]

In the database the bc field has a value of 1
the ab field a value of 2
sk / 3
mb /4
on / 5
pq / 6
ns / 7
nb / 8
nfl / 9
pei / 10
yk / 11
nwt / 12
nu / 13
al (thats an "L" as in AL but lower case) has a value of 14.

[catchup]

Thanks Dave you really fit your title, you really are a Super Moderator.

Thanks again, I am so happy:D