Problem with iif statement

[Enigma007]

Heya. I have the following relationship set up in my query:

[Unit Purchase] -> [Purchase Order]

I created the join such that All Unit Purchases are shown, and corresponding Purchase Orders that exist will be shown. Most Unit Purchases have a Purchase Order, but it is not necessary.

Purchase Orders have a supplier. I have a form that I want to allow to filter this query based on the supplier. I created this iif statement in the supplier field.

IIf([Forms]![Inventory Insert]![cboSupplier] Is Not Null,[Forms]![Inventory Insert]![cboSupplier],[Purchase Order].[Supplier>])

When this criteria was not present, all 1200 records would be shown. Those without a purchase order would, naturally, not have a supplier.

The problem is, when I added this criteria, it works properly when there is something chosen in the combo box, but the "ELSE" case causes problems. In the case that there is no Purchase order, the item is no longer displayed. This cuts my overall list down to 900 from 1200.

How could I rework this so that the cases with no purchase order could still be displayed when no supplier is specified?

Any help would be GREATLY apprecaited.

Thanks,
Enigma

 

[Enigma007]

I just realized that, not only does this iif statement remove the cases where there is not a purchase order, but it also removes the cases where the supplier is listed as NULL.

ANy thoughts?

 

[Enigma007]

Okay. To narrow down the problem. Apparently, any field that I have, if I use an iif structure, it removes the NULL values.

SO:

If I have my query that just displays ID and Purchase Order in [Unit Purchase], It will display all 1200 values. However, if I change the criteria in the [Purchase Order] field selection to :

iif([Test] = 1, 1, [Purchase Order])

If I enter 1 for Test, I get purchase order 1, but if I enter a different value for test, I only get a listing of Purchase Orders that are not null.

Why is this?

Okay. So... I guess my question is more general than above. How would I list them all instead of just non-NULLs when Test is not 1?

 

[BobJacksonNCI]

Enigma,

You need to ease up on the caffeine! (And I know about that...)

Null means "I don't know", so any query looking at a field that contains a Null condition (don't think of it as a value) does NOT include it because, "I don't know".

One way to deal with it is to identify a null and substitute a value.

Since the original question is unclear now, I'll just give a general fur instance:

IIF(Nz([fldSometimesNull],"")="",fldToSubsitute,fldSometimesNull)

If fldToSubstitute can also be Null, it must also have a value substituted.

A good explanation of Null is provided by Allen Browne

http://members.iinet.net.au/~allenbrowne/casu-12.html

HTH,
Bob

 

[PHV]

To test for null, use the IsNull function.
You may also consider the Nz function:
Nz(SomeVariable, DefaultValueIfNull)

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

 

[Enigma007]

Hehe. It has indeed been a coffee induced morning.

Thanks for the replies. I have never used the Nz function before, and I appreciate the introduction to that. Unfortunately, I am still running into issues displaying my entire list when I use the iif statement.

I will attempt to reillustrate my basic question:

I have one table. This table has an [ID] and a [Purchase Order] field. ID is required for all, Purchase Order can be Null.

If I wanted to show all purchase orders, unless a number was specified, In the [Purchase Order] field criteria I have:

iif(isNull([Input]),[Purchase Order],[Input])

When running the query if [Input] is specified, all records with that particular [Purchase Order] should be displayed. Otherwise, All records should be displayed.

When no value is input, however, this only prints the records that do have a purchase order. How do I get to display the records without a purchase order in this list as well?

Thanks.

 

[BobJacksonNCI]

Try:

iif(isNull([Input]),Nz([Purchase Order]),[Input])

Nz defaults to returning 0 when null is encountered and a value to substitute was not specified.

For best replies, copy/paste your complete SQL. If you have any other references to [Purchase Order] in the query, they could be causing the result.



HTH,
Bob

 

[Enigma007]

Thanks for the response

Unfortunately, this did not make help fix the issue. Logically, it makes no sense to me.

Here is the complete SQL:

SELECT [3-0 Unit Purchase].ID, [3-0 Unit Purchase].[PO>]
FROM [3-0 Unit Purchase]
WHERE ((([3-0 Unit Purchase].[PO>])=IIf(IsNull([Input]),Nz([PO>]),[Input])));

If I remove the where, I get 1200 records, if I have it in I only get 900 (all the non-null Purchase Orders).

Sorry to be such a pain!

 

[PHV]

In the SQL window, somewhere in the WHERE clause you have:
([Purchase Order] = iif(isNull([Input]),[Purchase Order],[Input]))
Replace it by this:
([Purchase Order]=[Input] Or IsNull([Input]))


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

 

[Enigma007]

Excellent. It does indeed work PH.

Thank you, everyone, for putting up with me. I really appeciate all of the help.

Sincerely,
Enigma