Hey everyone!!

I have an intranet which I am building and have come to the search part of it. What I have is basically 3 radio buttons choosing which column in the DB I want to search and two date fields searching between them.
What I want to know is how do I pass the radio button value to the results page so that it returns the values of that selected column?
EX) Choosing rad1 would search column a
Choosing rad2 would search column b ETC.

The code below is my search function and my recordset on one results page. The way the java is setup, it would take each different radio button to a different results page. The code is passing and appending to the URL for the AED_Master."exp", however I am receiving NO results in the recordset. Someone please help!!

SELECT DISTINCT (AED_Master.ACT_no) AS Expr1, Client.ACT_no, *
FROM ((ACT_Master RIGHT JOIN Client ON ACT_Master.ACT_No=Client.ACT_no)
INNER JOIN AED_master ON Client.ACT_no=AED_master.ACT_No)
INNER JOIN Models ON AED_master.Model_ID=Models.Model_ID
WHERE exp > ? AND exp < ?
AND Client.ACT_no = AED_Master.ACT_no;

Hi!

This is pseduocode, but shows the principle (assuming I've understood your query, apologies if I haven't):

Assuming that variable 'mycol' is the column you want to search (from the radio button set), choices being 'date1' 'date2' or 'date3' and startdate and enddate are the date range you are comparing against columns named "datecol1, datecol2 and datecol3, respectively, your SQL would be:

Select * from mytable where (mycol="date1" and datecol1>=startdate and datecol1<=enddate) or (mycol="date2" and datecol2>=startdate and datecol2<=enddate) or (mycol="date3" and datecol3>=startdate and datecol1<=enddate)

Hope that helps!

Hello Ian Webb,
You understand it, however I only want to compare one column at a time and that is the actual query I am using. If I use your query to run the page then I would have to believe it would look something like this:

SELECT DISTINCT (AED_Master.ACT_no) AS Expr1, Client.ACT_no, *
FROM ((ACT_Master RIGHT JOIN Client ON ACT_Master.ACT_No=Client.ACT_no)
INNER JOIN AED_master ON Client.ACT_no=AED_master.ACT_No)
INNER JOIN Models ON AED_master.Model_ID=Models.Model_ID
WHERE (mycol < "date1" AND mycol > "date2"
AND Client.ACT_no = AED_Master.ACT_no;

The three columns I am going to search are all "Date" fields in the DB. I am curious as well as to whether or not my SQL is correct in the WHERE clause?

Hi!

The WHERE clause basically works as a column selector. Note that it contains 3 clauses, separated by 'or':

where (mycol="date1" and datecol1>=startdate and datecol1<=enddate) or (mycol="date2" and datecol2>=startdate and datecol2<=enddate) or (mycol="date3" and datecol3>=startdate and datecol1<=enddate)

This means that it mycol = 'date1', then it searches datecol1, if it's 'date2' then it searches datecol2, etc.

Only one of the bracketed clauses needs to return true for the record to be selected and returned in your recordset.