search query logic question - showing nil values

[splreece]

Morning all,

Kind of a newbee question but I am getting into more complicated stuff and may have bypassed some fundamental query techniques.

I think the cause is that I want the user to fill in as many unbound boxes as they need (and leave unneeded ones blank), but the query is assuming that any blank unbound box means its subsequent search criteria must equal blank/Nil.




My setup means I have the same issue with both user criteria parts of my dbase:

- My search form needs to be able to search multiple criteria (8 different unbound boxes) and show results of any combination of those boxes whether the user completes them or not.
i.e. User or ID or Priority or Milestone.

- My report design form needs to be able to pull EXACT matches against the same multiple criteria (the same 8 different unbound boxes) into a report and show only what is selected.
i.e. user = exact match (then filter) id = exact match (then filer) priority = exact match.

In both cases I want the query to know that if the user doesn't complete a particular field that the query should bring back all available entries.



The issue I am having is that if the user doesn't fill in ALL the search or reporting boxes, the results are nil (I assume by leaving a search criteria blank is only accepts blank criteria).

Example of the Search form Query Design (all in different rows ensuring "OR" is taken into account)
[Forms]![frm_Search]![src_cmbpriority]
[Forms]![frm_Search]![src_milestones]


Example of the Report Query Design (all in the same criteria row to ensure that exact match only).

[Forms]![frm_ReportControl]![rc_Priority]
[Forms]![frm_ReportControl]![rc_AssignedTo]
[Forms]![frm_ReportControl]![rc_Status]



any thoughts....???

[CJ_London]

this

Forms]![frm_Search]![src_cmbpriority]

needs to be

(Forms]![frm_Search]![src_cmbpriority] OR Forms]![frm_Search]![src_cmbpriority] is Null)


note the brackets

[splreece]

ahh thank you very much
so remove the first and last [] and replace with () to wrap both into a single query.

[CJ_London]

it wil automatically add back the square brackets, my mistake when copy/pasting. Should be

([Forms]![frm_Search]![src_cmbpriority] OR [Forms]![frm_Search]![src_cmbpriority] is Null)