Filtering using multiple select drop-down menu

J

jmosow

I have searched but have not seen a clear cut answer on this. I have a
drop-down menu created using a custom query. I want to use this to
filter my selection on the page. Using single selections works fine.
If I use the multiple select option on the drop-down menu and change my
filtering custom query to do a SELECT * FROM TABLE WHERE (DBFIELD IN
:):FORMFIELD::)), I do not get any matches. I have seen some comments
where the ::FORMFIELD:: should be in single quotes. This does not seem
to work. I have also seen comments where you have to loop through the
array for the fields and build the search criteria, but I am not
skilled enough to know exactly how/where to do this.

Any help, particularly examples, would be greatly appreciated.

TIA
 
S

Stefan B Rusynko

The syntax IN is wrong in
SELECT * FROM TABLE WHERE (DBFIELD IN:):FORMFIELD::))
Should be more like say
SELECT * FROM TABLE WHERE DBFIELD = '::FORMFIELD::'

Presuming DBFIELD is the name of your actual field in the DB and FORMFIELD is the actual name of the form field you will need

1st Redo your DBRW to display all records and test it
Then right click it (select DBR properties) to modify it and select More Options - Criteria - Add
For the field name select your actual DB field from the dropdown - say: DBFIELD
For the value enter thr form field name you desire - say: FORMFIELD
- Select use this search form field and on the last screen of the DBRW select Add search Form




|I have searched but have not seen a clear cut answer on this. I have a
| drop-down menu created using a custom query. I want to use this to
| filter my selection on the page. Using single selections works fine.
| If I use the multiple select option on the drop-down menu and change my
| filtering custom query to do a SELECT * FROM TABLE WHERE (DBFIELD IN
| :):FORMFIELD::)), I do not get any matches. I have seen some comments
| where the ::FORMFIELD:: should be in single quotes. This does not seem
| to work. I have also seen comments where you have to loop through the
| array for the fields and build the search criteria, but I am not
| skilled enough to know exactly how/where to do this.
|
| Any help, particularly examples, would be greatly appreciated.
|
| TIA
|
 
J

jmosow

I tried the suggestions but this does not work. The problem is the
format of the SQL command. With a multiple select drop-down menu, if I
use this in my asp:

fp_sQry="SELECT * FROM ""Objects for HTML Query"" WHERE (HostSystem IN
('::HostID::'))"

The resulting query comes out as:

SELECT * FROM "Objects for HTML Query" WHERE (HostSystem IN ('J, Q'))

What it should be is that each value being separated a single quote.
It should actually be:

SELECT * FROM "Objects for HTML Query" WHERE (HostSystem IN ('J', 'Q'))

I don't know how to get the additional sinqle quotes on each parameter.
 
J

jmosow

I was able to fix the query with the following:
fp_sQry="SELECT * FROM ""Objects for HTML Query"" WHERE (HostSystem
IN
:):HostID::))"

However, this has to be done outside of Frontpage. Each time FrontPage
loads the file, it converts it back to the original statement, which
does not work.
 
K

Kathleen Anderson [MVP - FrontPage]

Switch to Code view and find the SQL in the gray-colored code. Make your
changes to the gray-colored code and save the changes while still in Code
view. You will notice that once you've saved your changes they are also made
to the maroon colored code - that is by design. The maroon colored code
should never be touched; it's generated by the gray code at save time.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
 
Top