default to all if criteria not selected

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have a form allowing user to select multiple criteria (ex. priority, status,
etc.) - which exectues a query to view the results. Will eventually tie this
to several reports. Problem: have to select a value in each of the 7 fields
in the form in order to get results. Need to be able to default to "all" if
criteria is not selected for some of the fields. Any help would be
appreciated. Thank you
 
How are you passing the selected values to the query? Does your query have
criteria that reference the value of the form fields?

If so, the way I usually work it is to put a hidden field on the form for
each input field and point the query criteria to these hidden fields. Then
when the user presses whatever command you have that runs the query (or
opens the report), I have code in the click event that looks at each
criteria field in the form. If the field (e.g., a textbox) has a value in
it, I move that value into the corresponding hidden field. If there is
nothing in the field, then I put an asterisk ("*") in the hidden field.
Finally, I make all criteria in the query to be "like" criteria (e.g., Like
forms!frmCriteria!txbHiddenPriority instead of just
forms!frmCriteria!txbPriority).

Doing it this way, if the value in a hidden field is "*", that criteria will
evaluate to Like "*", which means all values.
 
Thank you for the response. The query criteria does reference the value on
the forms. I do have the hidden fields and have code to execute as you have
described. My query criteria does not reference the "hidden" field -- so I
will try that. Thank you
 
Added "hidden" field to query criteria - still encountering problems. Am
wondering if it is the onclick action. Could you provide a sample of the
code? The fields are department name, mailcode, status, priority, assessment
type, program, and reposonsible person. Have also defined hidden fields on
the form. The user presses the "recommendations" command and the query
executes (the query uses the "Assessments and Actions Tbl")
 
Back
Top