IIF statement

[LilMissAttack]

Hi, I'm trying to pull something based on conditions. If the first zipcode field is blank, I want it to pull the 2nd, if that is blank I want it to move to a 3rd. It isn't pulling the 3rd option, here is what I have..what am I missing here? Thanks!

ZipCode:IIf(IsNull([dbo_query_Sales_Order_Delivery_Address_sls042]![Zipcode]),


[dbo_query_Delivery_Addresses]![Postcode],IIf(IsNull([dbo_query_Delivery_Addresses]![Postcode]),
[dbo_query_Customer_Master]![PostCode],[dbo_query_Sales_Order_Delivery_Address_sls042]![Zipcode]))

[June7]

Try:

Nz([dbo_query_Sales_Order_Delivery_Address_sls042]![Zipcode],
Nz([dbo_query_Delivery_Addresses]![Postcode],
[dbo_query_Customer_Master]![PostCode]))

EDIT: I like rpeare's concatenation suggestion from the next post. Like Of Course, Duh!

[rpeare]

Your iif statement is circular and not logically doing what you want.

For instance let's say this part of your statement evalutes as true (the item is null

IIf(IsNull([dbo_query_Delivery_Addresses]![Postcode])

You are automatically making it use

[dbo_query_Customer_Master]![PostCode]

which makes me ask this:

Is only ONE postal code going to be available? Or is there the possibility of there being a postal code in each field? If so is your priority

dbo_query_Sales_Order_Delivery_Address_sls042
THEN
dbo_query_Delivery_Addresses
THEN
dbo_query_Customer_Master

if only one will be active I'd do something like this

ZipCode: [dbo_query_Sales_Order_Delivery_Address_sls042]![Zipcode] &
[dbo_query_Delivery_Addresses]![Postcode] & [dbo_query_Customer_Master]![PostCode]

What this is going to do is append 3 different strings together and if 2 of the three strings are null you won't even notice them.

If you're doing a priority system it's a little more complex