Allowing Users to Select Criteria AND Fields

[vgnmnky]

I currently have a continuous form, with combo boxes on the footer allowing users to select records by setting criteria for particular fields, which are used as criteria in the select query (which the report is also based on).

However, as there are a lot of fields that they would like to search by (mostly date fields for different 'milestones' in a task/project - to check which are due/overdue etc), is there a way for the user to first select the field from a list, then the criteria, and pass this back to the select query? Or perhaps a different approach all together!

 

[rac2]

Stage 1. Build page showing report results of current query. Plus a form that asks for a choice of what criteria to use in the next report. Submit the form.
Stage 2. Build page showing same report results of same query. Plus a new form that has fields for the criteria selected above. Submit the form.
Stage 3. Build a page showing new report results for the new criteria.

The actual query is always the same and uses a pair of variables for each criterion. One variable is an indicator variable showing whether the criterion is used; the other variable is either null or the search value. Like so-

SELECT * FROM aTable
WHERE ( anyValueCriterion1
        OR
        column1 = valueCriterion1
      )

If the criterion is used anyValueCriterion is set to false in your ASP script; if it is not used set to true. When the indicator variable is true the column value doesn't matter because the condition is always true. When the indicator variaable is false the column value must match the search criterion for the row to be included in the result.

The Stage 1, 2, 3 approach is not really needed if you have enough space to show all possible criteria and can write clear instructions that only the fields of interest need be filled.

 

[vgnmnky]

Thank you for your reply. Unfortunately, I forgot to say that here at work, we're still stuck with Access97, and I think I need to work here for another 5 years before I get the chance to apply your solution! For now I think I'll just have to have a slightly busy form footer with all available fields, and work on it in slow time. But thank you again

 

[danvlas]

Try this sample:

ftp://ftp.artrom.ro/SearchForm.zip

I have just made it for someone in this forum, and you may find it useful...

Regards,

Daniel Vlas
Systems Consultant
danvlas@yahoo.com