Filter To Dropdown

[finkelman]

Hi,

I would like setup a filter that will take all the States found in the results and create a dropdown menu. Here is what my SQL statment looks like.

<% 'RETRIVE CURRENT VALUES FROM THE DATABASE

store_id = 10

sql_Select_customers = &quot;SELECT first_name, last_name, Address1, Address2, State FROM store_customers WHERE Store_id=&quot;&Store_id
rs_Store.open sql_Select_customers,conn_store,1,1
rs_Store.Close

%>

How can I create a dropdown menu based on all the states found in the search, while of course taking out duplicates.

Bellow is an example of how my table might look like, and what the results will be.

Table: store_customers
Joe1|Smith1|1011 11st|1st Floor|NY
Joe2|Smith2|1012 12st|2st Floor|NJ
Joe3|Smith3|1013 13st|3st Floor|CA
Joe4|Smith4|1014 14st|4st Floor|NY
Joe5|Smith5|1015 15st|5st Floor|NJ
Joe6|Smith6|1016 16st|6st Floor|PA

This would be the dropdown results.

<select name=state>
<option>NY
<option>NJ
<option>CA
<option>PA
</select>


Thanks

 

[GIGN]

You need to slip DISTINCT in there somewhere. If you are only using distinct states - can't tell from your demo, but you ould jut grab them then.

&quot;SELECT DISTINCT State FROM store_customers WHERE Store_id='&quot;& Store_id & &quot;'&quot;

or I suppose a filter may come in handy

http://www.devguru.com/Technologies/ado/quickref/recordset_filter.html

I have only ever got the * wildcard to work, just as an aside.