ignor criteria if null?

[Robert H]

I have two criteria in a query that are set by ComboBoxes in a form.
The query works correctly when something is selected. Sometimes one
of the Comboboxes will not be selected and I assume the value will be
null. I've tried variations of the following expression in the
criteria box for the criteria that may not exists.

IIf([Forms]![frmBumpConfig]![cboCode] Is Not Null And <>"",[Forms]!
[frmBumpConfig]![cboCode],Null)

I have also tried "" in the «falsepart» of the IF function.

The results are always empty when the cboCode combobox is not
activated.

I just want the criteria for the Code field to be ignored is there is
no selection.

 

[Jeff Boyce]

Robert

By setting the criterion to Null when there is nothing in the form control,
you are telling the query to find records in which that field has a "value"
of Null. I don't believe that's what you are describing as what you want.

One approach would be to add code behind a button click on the form, in
which you dynamically create a WHERE clause for the SQL statement. If
nothing is in the control, don't build a WHERE clause for that variable. If
something is, include a WHERE clause.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have two criteria in a query that are set by ComboBoxes in a form.
The query works correctly when something is selected. Sometimes one
of the Comboboxes will not be selected and I assume the value will be
null. I've tried variations of the following expression in the
criteria box for the criteria that may not exists.

IIf([Forms]![frmBumpConfig]![cboCode] Is Not Null And <>"",[Forms]!
[frmBumpConfig]![cboCode],Null)

I have also tried "" in the «falsepart» of the IF function.

The results are always empty when the cboCode combobox is not
activated.

I just want the criteria for the Code field to be ignored is there is
no selection.

 

[Jeff Boyce]

Robert

.... and I suppose another way to do this would be to add multiple "OR"
conditions in your design view of the query. You'd need to add as many
criteria rows as you have valid combinations of selection/non-selection.
This could get very complex very quickly if you have more than a couple
comboboxes...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two criteria in a query that are set by ComboBoxes in a form.
The query works correctly when something is selected. Sometimes one
of the Comboboxes will not be selected and I assume the value will be
null. I've tried variations of the following expression in the
criteria box for the criteria that may not exists.

IIf([Forms]![frmBumpConfig]![cboCode] Is Not Null And <>"",[Forms]!
[frmBumpConfig]![cboCode],Null)

I have also tried "" in the «falsepart» of the IF function.

The results are always empty when the cboCode combobox is not
activated.

I just want the criteria for the Code field to be ignored is there is
no selection.

 

[Allen Browne]

Robert, here's an example of what Jeff is suggesting:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The end of the article explains how you can do it in the query if you
prefer.

 

[Robert H]

thank to you both, I will try your suggestions out, tomorrow