We help IT Professionals succeed at work.

IIF in access query

Bobby
Bobby asked
on
153 Views
Last Modified: 2018-01-17
I need to add to the query below an IIF statement which will make EstShipCost $0.00 if products.localdiscontinued=true. By the way, InventoryProfile.EstShipCost is currently null if products.localdiscontinued=true, and my code is breaking because I don't want any null values passed. There needs to be a number, and $0.00 will suffice. InventoryProfile.EstShipCost is a Currency field.

SELECT KWS.SKU, KWS.Price, BB_stock_status.shipping, InventoryProfile.EstShipCost, InventoryProfile.MAPPricingAmount, Products.LocalDiscontinued
FROM ((KWS INNER JOIN Products ON KWS.SKU = Products.SKU) INNER JOIN InventoryProfile ON KWS.SKU = InventoryProfile.LocalSKU) INNER JOIN BB_stock_status ON KWS.SKU = BB_stock_status.product_id;

Open in new window

Comment
Watch Question

Mike EghtebasDatabase and Application Developer

Commented:
Nz([ColName],0)

When ColName is null, NZ() will return 0

I am looking to see how it could be used in your SQL.

Author

Commented:
Here is the VB code that breaks when a null value is encountered...

example
Mike EghtebasDatabase and Application Developer

Commented:
SELECT KWS.SKU
	, KWS.Price
	, BB_stock_status.shipping
	, IIF(Products.LocalDiscontinued = False, InventoryProfile.EstShipCost, 0) As EstShip_Cost
	, InventoryProfile.MAPPricingAmount
	, Products.LocalDiscontinued
FROM (
	(KWS INNER JOIN Products 
	ON KWS.SKU = Products.SKU
	) 
	INNER JOIN InventoryProfile 
	ON KWS.SKU = InventoryProfile.LocalSKU
	) 
	INNER JOIN BB_stock_status 
	ON KWS.SKU = BB_stock_status.product_id;

Open in new window

Mike EghtebasDatabase and Application Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Mike,

So that query you posted will only make EstShipCost 0 if products.localdiscontinued=true, Correct? If products.localdiscontinued=false, I need to pass whatever the value is in EstShipCost along, and not have it be 0.
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Mike,

Not sure what you mean by ShipCost = rs!EstShip_Cost... the field is estshipcost, no underscore.

Dale,

Same question for you... that query you posted will only make EstShipCost 0 if products.localdiscontinued=true, Correct? If products.localdiscontinued=false, I need to pass whatever the value is in EstShipCost along, and not have it be 0.
Mike EghtebasDatabase and Application Developer

Commented:
Yes, your description is correct.

Author

Commented:
Dale,

I like the idea of just changing the code, but same question as above applies.

Author

Commented:
Mike,

Not sure what you mean by ShipCost = rs!EstShip_Cost... the field is estshipcost, no underscore.

Author

Commented:
Dale,

If I just change the line in VB to:

ShipCost = NZ(rs!EstShipCost, 0)

Then if it finds a null value in EstShipCost it will insert a 0, otherwise it will pass along whatever value is in EstShipCost? Is all that correct?
Mike EghtebasDatabase and Application Developer

Commented:
We have changed EstShipCost in our query to EstShip_Cost. Therefore, we need to change
ShipCost = rs!EstShipCost to ShipCost = rs!EstShip_Cost

Author

Commented:
Trying Dales idea of altering the VB to ShipCost = NZ(rs!EstShipCost, 0), I will come back when I know how it works.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
@QMBB,

Ref your comment, yes, that line of code with the modification to NZ(rs!EstShip_Cost, 0) would return the value in the EstShip_Cost field if there is one, or a value of zero if rs!EstShip_Cost IS NULL.

Dale

Author

Commented:
Dale,

Nice. I'm testing that now, without the underscore. Will be back when I know it works... takes awhile to run across one of those null cases, and I'm running it fully to be sure it works in production.
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Pat, thanks much for the explanation. It did help me to wrap my head around what NZ does.
Mike EghtebasDatabase and Application Developer

Commented:
re:> Not sure what you mean by ShipCost = rs!EstShip_Cost... the field is estshipcost, no underscore.

When you have a code like

ShipCost = rs!EstShipCost

This means EstShipCost exist in the recordset rs. But because in our query, we had used:

IIF(Products.LocalDiscontinued = False, InventoryProfile.EstShipCost, 0) As EstShip_Cost

Therefore we no longer have a column named EstShipCost (in our rs recordset). This column, therefore, is known by its alias name: EstShip_Cost

This is why, we had to use:

to ShipCost = rs!EstShip_Cost

With my modiffied query (which includes the IIF() you have asked for), using ShipCost = rs!EstShipCost you will give you an error saying EstShipCost is an unknown column name (or something to that effect).

Mike

Author

Commented:
ah, I see. Thanks very much for the clarification and the help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.