Criteria Form or Null

[Vittles]

I have a query that uses a Criteria form/combo box to pull records. If the Criteria form has a value selected just those records with that value come up. However, I also want to be able to not select a criteria and only have those records who have 'Null' values to come up.

The query criteria field that is used is a text field that has an input mask that is a combination of letters and numbers.

I know that if you want to show all records the formula in this case would be:
IIF(IsNull([Forms]![SEP Selection Form]![SEP Number]),[SEP Number],[Forms]![SEP Selection Form]![SEP Number])

What would the formula be to pull only those records that do not have data entered into the field if criteria form is not filled in?

I have tried:
IIf(IsNull([Forms]![SEP Selection Form]![SEP Number]),IsNull([SEP Number]),[Forms]![SEP Selection Form]![SEP Number])

with no luck....

 

[PHV]

In the WHERE clause of the SQL code:
WHERE ...
AND Trim([SEP Number] & "") = Trim([Forms]![SEP Selection Form]![SEP Number] & "")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Vittles]

Perfect - Thank you!