Show All in Query Autonumber Criteria If Statement

  • Thread starter Thread starter justinschmidt
  • Start date Start date
J

justinschmidt

I have an autonumber field in a query that I want to put criteria on.

When a text box on a form has a value (say 1, 2, or 3), I want the
query to run with the autonumber criteria set to this value. When there
is no value in this text box, I want it to return all the records.

I'm trying to use this line

IIf(IsNull([Forms]![Main
Menu]![ProjectIDSet]),WHATGOESHERE,[Forms]![Main Menu]![ProjectIDSet])

I've tested this line, and it works perfect, except I can't figure out
what to use as the 'Value if True' item. I can't use something like Not
Null because it is an autonumber field. I tried using <>0 but that
didn't work. I can't imagine this being complicated, but I'm completely
stumped.
 
You can't do what you're trying to do using an IIf statement.

You want SQL that looks like:

WHERE (MyAutoNumber = [Forms]![Main Menu]![ProjectIDSet] OR [Forms]![Main
Menu]![ProjectIDSet] IS NULL)

You can get this by putting

[Forms]![Main Menu]![ProjectIDSet] OR [Forms]![Main Menu]![ProjectIDSet] IS
NULL as the criteria in the query builder.
 
What about this.....

I have a check box on a form

When the check box is clicked, I want a listbox to filter
When the check box is not clicked, I don't want the listbox to filter

Everything I try is only filtering according to the textbox selection.
 
Back
Top