search fields

hello


I have a search page with 5 drop down menus(where data pulled from access database.

how do i give the select query , which will bring out results depending on the search critieria/fields set by user.

for example

the fields are first name, last name ,city, age,education.

if a particular city is choosen i want al the rows that matches the city filed.

if age and city are choosen .i want the results which has the age in that particular citty.

Thanks for your help

 

Basically you need the following for the first case.

SELECT * FROM <table name> WHERE <city field> = '<city-drop-down-value>'

Code (markup):

You will need to insert the table name and the field name. Replace everything (including the angle brackets) with the actual names.

To filter the results on additional values you need to add at the end additional clauses:

 AND <age-field> = '<age-drop-down-value>'

Code (markup):

To give

SELECT * FROM <table name> WHERE <city field> = '<city-drop-down-value>' AND <age-field> = '<age-drop-down-value>'

Code (markup):

 

I dont think it makes sense to have 5 dropdowns in the first place.

The search page should allow the user to (optionally) enter the firstname and the lastname and choose the city, age and/or education.

If you have 200 names in your DB, are you going to list all these names in your dropdowns? I dont think so!

Now for your sql statement should look like this:

sqlstm = "SELECT * FROM tableName WHERE "
sqlstm = sqlstm & "firstnameField LIKE '%" & firstname & "%' AND "
sqlstm = sqlstm & "lastnameField LIKE '%" & lastname & "%' AND "
sqlstm = sqlstm & "ageField = " & age & " AND "
sqlstm = sqlstm & "cityField = '" & city & "' AND "
sqlstm = sqlstm & "educationField = '" & education & "'"

Let me know if you dont understand.