Quick question about search fields+drop-down menus...

G

Guest

Hello all, I was hoping it wouldn't come to this, but alas...

What I am trying to do is add search functionality to a website using
Frontpage 2003 and Microsoft Access (the db is already set up, so no problems
there).

I can run a normal search, and return the results.

I can search by category with a drop-down menu and return the results.

However, I would like to be able to do both, i.e., use a "Search in"
drop-down menu so users can narrow their searches to category X, Y, or Z.

Once the category is selected, have them be able to do a text search within
the designated category with a box e.g., Search "X" for "information".

I have been banging my head on this one for about four hours now, I have
read article 235554 MANY times, tried the steps, tried adapting what I've
learned and then tried the steps again, but to no avail...same with the pages
from Spider Web Woman Designs...

Any direction here would be much appreciated.

jerod
 
K

Kathleen Anderson [MVP - FrontPage]

Here's the form (I didn't generate the dropdown list from the databse - I
did this before I learned how to do that):

<form botid="0" method="POST" action="municipalityprogram2.asp">
<table border="0">
<tr>
<td><label for="Municipality"><b>Municipality</b></label></td>
<td><input type="TEXT" title="Enter the name of the municipality you
are searching for here" name="Municipality"
value="<%=Request("Municipality")%>" size="20"></td>
<td><b><label for="ProgramArea">Program Area</b></label></td>
<td><select size="1" name="ProgramArea">
<option value="Choose One From This List">Choose One From This
List</option>
<option value="Corrections">Corrections</option>
<option value="Cultural Resources (arts, historic
preservation)">Cultural
Resources (arts, historic preservation)</option>
<option value="Economic Development">Economic Development</option>
<option value="Education/Libraries">Education/Libraries</option>
<option value="Environment">Environment</option>
<option value="General Government/Administration">General
Government/Administration</option>
<option value="Health/Social/Human Services">Health/Social/Human
Services</option>
<option value="Housing">Housing</option>
<option value="Justice/Public Safety/Military">Justice/Public
Safety/Military</option>
<option value="Parks/Recreation/Open Space">Parks/Recreation/Open
Space</option>
<option value="Transportation">Transportation</option>
<option value="Urban/Community Development">Urban/Community
Development</option>
<option value="Other">Other</option>
</select></td>
</tr>
</table>
<br>
<input type="Submit"><input type="Reset"><!--webbot bot="SaveAsASP"
clientside
suggestedext="asp" preview=" " startspan --><!--webbot bot="SaveAsASP"
endspan --><p><a href="../information.htm#municipalities">Definition of
Municipality</a></p>
<p><a href="../information.htm#programarea">Definition of Program
Area</a></p>
</form>

and here's the SQL from the page that it posts to (you could probably just
copy and paste something similar into the custom query window of the DRW):

s-sql="SELECT
ProgramArea,RecordNum,Date,ItemNumber,SplitItem,Session,PA_SA,ActNumber,Sect
ion,Recipient,Municipality,FundsUse,Format(Total_Earmarking,'$#,##0.00') AS
DollarField1,Format(Allocation_Amount,'$#,##0.00') AS
DollarField2,Format(Previous_Allocation,'$#,##0.00') AS
DollarField3,Format(Balance_Unallocated,'$#,##0.00') AS
DollarField4,Any_Previous,Format(How_Much,'$#,##0.00') AS
DollarField5,FundNum,AgencyNum,SID,Project_Num,Description FROM &quot;Bond
Commission Data Input&quot; WHERE (Municipality = '::Municipality::' AND
ProgramArea = '::programArea::') ORDER BY Date DESC, ItemNumber ASC"



--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx
 
G

Guest

and here's the SQL from the page that it posts to (you could probably just
copy and paste something similar into the custom query window of the DRW):

s-sql="SELECT
ProgramArea,RecordNum,Date,ItemNumber,SplitItem,Session,PA_SA,ActNumber,Sect
ion,Recipient,Municipality,FundsUse,Format(Total_Earmarking,'$#,##0.00') AS
DollarField1,Format(Allocation_Amount,'$#,##0.00') AS
DollarField2,Format(Previous_Allocation,'$#,##0.00') AS
DollarField3,Format(Balance_Unallocated,'$#,##0.00') AS
DollarField4,Any_Previous,Format(How_Much,'$#,##0.00') AS
DollarField5,FundNum,AgencyNum,SID,Project_Num,Description FROM "Bond
Commission Data Input" WHERE (Municipality = '::Municipality::' AND
ProgramArea = '::programArea::') ORDER BY Date DESC, ItemNumber ASC"
Click to expand...

Well, I'm so close that I can taste it...

The original posted query was bugged for some reason and my knowledge of SQL
is very limited, so I've been asking friends about this...unfortunately their
knowledge of Access and FrontPage is limited.

My SQL query has evolved into this:

SELECT
Product_Name,Quantity,Chemical_Family,OEL,Health_Risk,Control_Measures_Hand,Control_Measures_Eye,Control_Measures_Respiratory,
Control_Measures_Other,First_Aid_Initial_Actions,Spills_Cleanup,Flammable_Spill_Code,Incompatible_With
FROM Results WHERE Service_Unit = '1' AND Product_Name = '2' ORDER by
Product_Name DESC

(Where Service_Unit was selected from the drop-down menu and Product_Name
was inputted into the text box in search.asp and moved to search2.asp using
<form botid="0" method="POST" action="search2.asp">)

It's no longer bugged, and the query is running fine when I try to run it in
Access, although not returning any results, of course.

I'm adding the statement in the custom query in the DRW, but when I try the
search it's not returning any results.

Thank you so much for your patience and continued support :)

jerod
 
G

Guest

Kathleen Anderson said:
To start with, try putting parentheses around your search criteria; like
this:

WHERE (Service_Unit = '1' AND Product_Name = '2')
Click to expand...

Done...and still no results back.
 
S

Stefan B Rusynko

Are Service_Unit and Product_Name numeric or text fields?
Run that qry in Access and see if you get any results
as WHERE (Service_Unit=1 AND Product_Name=2) if numeric
or WHERE (Service_Unit='1' AND Product_Name='2') if text
 
G

Guest

Stefan B Rusynko said:
Are Service_Unit and Product_Name numeric or text fields?
Run that qry in Access and see if you get any results
as WHERE (Service_Unit=1 AND Product_Name=2) if numeric
or WHERE (Service_Unit='1' AND Product_Name='2') if text
Click to expand...

Both fields are text fields, so I have been using the single quotes.

I ran the query in Access and am getting no results back. Forgive my
ignorance here, but is that not to be expected? I was under the impression
that the query is dependant on user defined information from the first asp
page.

Thanks for your reply,

jerod
 
S

Stefan B Rusynko

Yes
- but if you can't generate / run a query in Access to get the expected results (and look at the SQL generated in access), you
probably will never get any results thru a web based interface because your data does not match the query

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

database search, dropdown selection. 1
Chart Title change with Drop Down Selection 2
using a drop-down list in a database results search form - ? 1
Select drop down menu option using libre-calc macro 0
Database drop down menus 5
Search with a drop down menu 4
DROP DOWN MENUS 7
DB results search form issues 3

Top