Criteria field set by IIf Statement.

[Guest]

Ok, here we go. I have a form named 1_GlobalVariables that I use to hold
query filters. In my queries, I put these globalvariable filters into the
criteria to filter the results. The column in the query is named [Product
Manager]. The following is in the criteria statement:

IIf([Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null,[Product
Manager],[Forms]![1_GlobalVariables]![Product Mgr Filter])

This statement works fine.
1) If the [Product Mgr Filter] has been set to something, then the query
works.
2) If the [Product Mgr Filter] is not set to something, then the query works.
3) If the [Product Mgr Filter] is not set to something and [Product
Manager] is null, the query does not work.

I know that the problem is that I need to add an "or is null", but I do not
know how!
I have tried the following and they do not work...

IIf([Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null,iif([Product
Manager] is null,"Is Null",[Product
Manager]),[Forms]![1_GlobalVariables]![Product Mgr Filter])

 

[John Spencer (MVP)]

You can try

Field: [Product Manager]
Criteria: [Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null OR
[Forms]![1_GlobalVariables]![Product Mgr Filter]

Access will reformat this when you save the query, but in most cases it will
work for you.

 

[Guest]

John, it works, But... access reformatted it like you said and used the "OR"
line in the query design view. Now, I actually have 8 global variable
filters in this query... see where I am going. 2^8 = 256 "Or" lines were
created in the design view. This makes it very difficult to modify the
query. I would prefer to use an IIF statement, something like I have below -
this would make the query much easier to modify. Is there any way to make
the "Is Null" part of the below iff statement be interperated as I Intend, by
access?

IIf([Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null,iif([Product
Manager] Is Null,"Is Null",[Product
Manager]),[Forms]![1_GlobalVariables]![Product Mgr Filter])

Isn't there an access function that makes the format of a statement correct?
Like when you type Wi* in the criteria field, access changes it to Like "Wi*"

Thanks.
Steve

You can try

Field: [Product Manager]
Criteria: [Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null OR
[Forms]![1_GlobalVariables]![Product Mgr Filter]

Access will reformat this when you save the query, but in most cases it will
work for you.

Ok, here we go. I have a form named 1_GlobalVariables that I use to hold
query filters. In my queries, I put these globalvariable filters into the
criteria to filter the results. The column in the query is named [Product
Manager]. The following is in the criteria statement:

IIf([Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null,[Product
Manager],[Forms]![1_GlobalVariables]![Product Mgr Filter])

This statement works fine.
1) If the [Product Mgr Filter] has been set to something, then the query
works.
2) If the [Product Mgr Filter] is not set to something, then the query works.
3) If the [Product Mgr Filter] is not set to something and [Product
Manager] is null, the query does not work.

I know that the problem is that I need to add an "or is null", but I do not
know how!
I have tried the following and they do not work...

IIf([Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null,iif([Product
Manager] is null,"Is Null",[Product
Manager]),[Forms]![1_GlobalVariables]![Product Mgr Filter])

 

[John Spencer (MVP)]

Another way to handle this, but a bit slower is to force the null value to some
value that is never in the database. The reason it will be slower is that the
index for the field you are searching on (if the index exists) cannot be used
and Access will have to look through the entire table to find matches.

Something like the following if the field is a text field

Field: ProductManager: NZ([Product Manager],"")
Criteria: Like Nz([Forms]![1_GlobalVariables]![Product Mgr Filter],"*")

IF the field is numeric, then this is a little more complex but it can be
handled by searching for a value between two limits (say you have manager id as
a positive integer between 1 and 32767. Then you could use something like the following.
Criteria: Between Nz([Forms]![1_GlobalVariables]![Product Mgr Filter],0) AND
Between Nz([Forms]![1_GlobalVariables]![Product Mgr Filter],32767)

John, it works, But... access reformatted it like you said and used the "OR"
line in the query design view. Now, I actually have 8 global variable
filters in this query... see where I am going. 2^8 = 256 "Or" lines were
created in the design view. This makes it very difficult to modify the
query. I would prefer to use an IIF statement, something like I have below -
this would make the query much easier to modify. Is there any way to make
the "Is Null" part of the below iff statement be interperated as I Intend, by
access?

IIf([Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null,iif([Product
Manager] Is Null,"Is Null",[Product
Manager]),[Forms]![1_GlobalVariables]![Product Mgr Filter])

Isn't there an access function that makes the format of a statement correct?
Like when you type Wi* in the criteria field, access changes it to Like "Wi*"

Thanks.
Steve

You can try

Field: [Product Manager]
Criteria: [Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null OR
[Forms]![1_GlobalVariables]![Product Mgr Filter]

Access will reformat this when you save the query, but in most cases it will
work for you.

Ok, here we go. I have a form named 1_GlobalVariables that I use to hold
query filters. In my queries, I put these globalvariable filters into the
criteria to filter the results. The column in the query is named [Product
Manager]. The following is in the criteria statement:

IIf([Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null,[Product
Manager],[Forms]![1_GlobalVariables]![Product Mgr Filter])

This statement works fine.
1) If the [Product Mgr Filter] has been set to something, then the query
works.
2) If the [Product Mgr Filter] is not set to something, then the query works.
3) If the [Product Mgr Filter] is not set to something and [Product
Manager] is null, the query does not work.

I know that the problem is that I need to add an "or is null", but I do not
know how!
I have tried the following and they do not work...

IIf([Forms]![1_GlobalVariables]![Product Mgr Filter] Is Null,iif([Product
Manager] is null,"Is Null",[Product
Manager]),[Forms]![1_GlobalVariables]![Product Mgr Filter])