Query with IIF and Null entries

[JustinB]

I need some help.

I have a form that feeds information to a query which in turn sets up a report. For clarity i will list out in basic terms what I have

Input Form
- Check Box to activate/De-activate a text field [chk-active]
- Text field for a parameter [txt-Parameter]

the query has a column that has null values from the originating table. These values will be added a t a later date, but need to be queried and reported at some business intervals.

In the query criteria for this column, I have the following Criteria

Like IIF([Forms]![ReportGenerator]![chk-Active]=0, "*", [Forms]![ReportGenerator]![txt-Parameter])[/I][/I]

I have tried for the last 5 hours to figure out how to write the formula to be able to get the blank entries to show up as well but have had no luck.

Any guidance or comments will be greatly appreciated. Please let me know if more information is needed

 

[Pat Hartman]

You don't need the checkbox.

Where MyField = Forms!ReportGenerator![txt-Parameter] OR Forms!ReportGenerator![txt-Parameter] Is Null

If you really do need Like because the user is entering only a partial value then -

Where MyField Like "*" & Forms!ReportGenerator![txt-Parameter] & "*" OR Forms!ReportGenerator![txt-Parameter] Is Null

 

[JustinB]

This kind of works. I am assuming that the changes need to be done in the SQL view of the query and not in the Designer:Builder section. Correct?

If I do this on one field it works, but to complicate matters more, I have two independent fields that need the same operation.

Here is the breakdown of the fields

Master List Table:
MasterList.BaseNumber - No Nulls in this field
MasterList.EN - nulls exist in this field

User Form
The form that has the parameters has two fields
[Forms]![ReportGenerator]![txt-BN]
[Forms]![ReportGenerator]![txt-EN]

The user can enter into either one or both fields. I need to be able to generate a query that returns all results based on any of the following combinations of user entries

No txt fields - All results including nulls in MasterList EN field
1 txt field - All filtered results including nulls MasterList EN field
2 txt fields - All filtered results including nulls in MasterList EN field

Every time I write the SQL, I can get the BaseNumber to work properly, but when I jsut enter in an EN , I get nothing.

Any suggestions?

 

[JustinB]

Ok,
So I got the SQL query to do the single txt entry filtering, but I ma having trouble getting a filter to work where both form text boxes are filled and both fields are filtered.

Here is my SQL statement:
WHERE (((MasterList.[Base Number])=[Forms]![Report Generator]![txt-BNPick])) OR ((([Forms]![Report Generator]![txt-BNPick]) Is Null)) OR (((MasterList.EN)=[forms]![Report Generator]![txt-EN])) OR ((([Forms]![Report Generator]![txt-EN]) Is Null)) OR [MasterList].[Base Number] = [Forms]![Report Generator]![txt-BNPick] AND [MasterList].[EN]=[Forms]![Report Generator]![txt-EN];

 

[Cronk]

I can see a lot of brackets in the first two parts of the query but none in the last part, so you have a lot of ORs and one AND at the end that has to be true for anything to be included in the query output.

 

[Pat Hartman]

Whenever you use AND, OR, and NOT in the same expression, you will need to take to use parentheses to ensure that the expression is evaluated as you intend it to be. So to expand on what I originally posted -

Where (MyField = Forms!ReportGenerator![txt-Parameter] OR Forms!ReportGenerator![txt-Parameter] Is Null)
AND (MyField2 = Forms!ReportGenerator![txt-Parameter2] OR Forms!ReportGenerator![txt-Parameter2] Is Null)
AND ...
It is not clear to me whether you want to "and" the arguments or "or" them so you may need to change the AND to OR but notice how the parentheses enclose the Or expression. That's because you want (a =formfield or formfield Is Null) to be evaluated as a set without getting sucked into an AND that connects it to another expression.

Normal order of precedence is NOT, AND, OR so

a or b and c will be evaluated as
a or (b and c) so if you really ment
(a or b) and c - you MUST use parentheses to specify how the expression should be evaluated.