Query question with an IIF

[BenTitus]

Hello,
I am writing a query that links a Sum of Variation table to a Sales Table. The Field that links the two is the Sales Order field. The problem is that not all Sales_Orders in the sales table are in the Variation table. When i do a search with a paramater not all the records show, only the ones in botht tables . What i am asking is if there is a way to make all sales_orders that are not on the variation table = 0. I was thinking an IIF would work but i havent had any luck with it.

 

[BarkingFrog]

You need to do an outside join on the Sales Order field between the two tables. This will bring back everything that is in the Sales table that meets your criteria regardless if it is in the Variation table or not.

 

[tledwards]

You need to change the Join Properties of the tables. To do this open the Query. Right click on the join line connecting the two tables. Then select Join Properties. Select the option to show all records for Sales Table.

 

[BenTitus]

Wow can't believe i forgot that... Thanks works great

 

[BenTitus]

I ran into another problem. It shows all the records now but i am trying to make the null variances to equal 0. But when i try it all i am doing is making the TrueMargin equal 0. It seems like i am close but can't quite get it. My syntax currently looks like this:
IIf(Variation_Sum.SumOfVARIANCE Is Null,0,(tbl_Gross_Margin.STD_MARGIN-Variation_Sum.SumOfVARIANCE)) AS True_Margin

 

[BenTitus]

Nevermind i figured it out with the NZ function