Link to home
Start Free TrialLog in
Avatar of Bobby
BobbyFlag for United States of America

asked on

2 IIF's in Access query

I have the following in an Access query and it works fine:

IIf(bb_stock_status.shipping="1",Round(20*(products.price),0)/20,Round(20*(products.price+inventoryprofile.estshipcost),0)/20)

Open in new window


I need to add another If statement... if InventoryProfile.MAPPricingAmount <> $0.00 or <> Is Null, use the amount in that field as products.price.

So, the final price output will be one of three possibilities.
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

iif(Profile.MAPPricingAmount <> $0.00 or nz(Profile.MAPPricingAmount,'')='', products.price, IIf(bb_stock_status.shipping="1",Round(20*(products.price),0)/20,Round(20*(products.price+inventoryprofile.estshipcost),0)/20) )

Open in new window

Avatar of Bobby

ASKER

Thanks. Looks like I failed to mention that the final price including the MAP price needs to include InventoryProfile.EstShipCost, just like the end of the first code shows.

And this new MAP pricing stuff needs to round to the nearest nickle, like the other 2 do.
could you please revise the following to your liking?
I need to add another If statement... if InventoryProfile.MAPPricingAmount <> $0.00 or <> Is Null, use the amount in that field as products.price.
Avatar of Bobby

ASKER

Yes, sorry...

if InventoryProfile.MAPPricingAmount <> $0.00 or <> Is Null, use the amount in that field plus InventoryProfile.EstShipCost as the FinalPrice (in the query). I attached a picture to show what the query looks like as of now.

User generated image
Avatar of Bobby

ASKER

So the first part of the query takes just products.price and rounds it to the nearest nickle to use as the FinalPrice if bb_stock_status.shipping = 1. The second part of the query, if that first part wasn't met, uses products.price + inventoryprofile.estshipcost, rounded to the nearest nickle, as the FinalPrice. What I need to do is also see if inventoryprofile.mappricingamount exists (anything other than null or $0.00), and if it does, use that amount + inventoryprofile.estshipcost as the FinalPrice.
SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PatHartman
Change this:

iif(Profile.MAPPricingAmount <> $0.00 or nz(Profile.MAPPricingAmount,'')='', ....

To this:

iif(nz(Profile.MAPPricingAmount,0) = 0, ....

Streamline to just use the Nz() function which will natively return 0 for numeric datatypes (NZ = Null to ZERO) when they are null.  Having it return a ZLS just confuses Access into thinking that you want a string rather than a number as the result of the IIf().
Avatar of Bobby

ASKER

Whatever the number ends up being, it needs to go into the FinalPrice field of table KWS, as the query shot shows. What you have here will do that?
Avatar of Bobby

ASKER

just so we're clear, that table is called InventoryProfile, not just Profile.
Avatar of Bobby

ASKER

Mike,

Is what you're doing rounding to the nearest nickle? Doesn't look like it just viewing the code...
Avatar of Bobby

ASKER

Pat,

InventoryProfile.MAPPricingAmount is kinda screwy as far as table design... it either has a real dollar value, a $0.00, or it's null.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bobby

ASKER

Dale,

I appreciate the logic of putting it into a function, but I'm severely under the gun and for now I just have to get this working via the same method I'm using. One day soon I do need to go through and clean things up, but for today, there's no time. You're absolutely right though.
InventoryProfile.MAPPricingAmount is kinda screwy as far as table design... it either has a real dollar value, a $0.00, or it's null
There is nothing screwy about that.  The vast majority of my numeric fields default to null because 0 has a meaning and it is usually important to disginguish between an unknown value -- null and a known value -- 0.  If everything defaults to 0, then a teacher can't prepopulate a table with test results.  He has to add them one at a time when he knows the actual result.  Adding them up front as 0 will adversely affect grade average calculations until all scores are entered.  Foreign keys are especially problematic since 0 is almost certainly going to be an invalid value and you don't want to default to something known to be invalid.

The $ is simply a formatting character.  That isn't stored in the data.  That is a presentation affect.  The actual data type you are using must be a numeric data type if you expect to do arithmetic or sort numerically.
Avatar of Bobby

ASKER

Thanks Pat. Can you please provide a finished example of what you suggest?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bobby

ASKER

Agreed. I will use all provided solutions and split the points accordingly. Still doesnt look like what Mike supplied will round the first result, but I'll figure it out.
Avatar of Bobby

ASKER

I couldnt use what Pat suggested because it gave a data type error. Im using what mike suggested and  it works but only returns the new IIF (MAP pricing etc). In other words, the two that worked before return nothing. Obviously the end of the new IIF isnt closed. I'll have to revert to a backup and keep working on this to get it working.
Avatar of Bobby

ASKER

I added Dales function but don't know how to reference the query and update the correct table. Pardon the newbness but that's why I'm posting.
In order to help you debug this, we need to see the exact SQL you are using or if you can post the database with test data, that would be even more helpful.  Just make sure to make the path to reproducing the problem clear so we don't have to spend a lot of time trying various forms/buttons.
Can you explain exactly what it is you are trying to do?  Your original post simply said:

"I have the following in an Access query and it works fine:...I need to add another If statement... "

My assumption was that you simply wanted to create a computed column in your query based on several other values in the query.  If that is the case you would simply add a column to the query like:

FinalPrice: fnFinalPrice(bb_stock_status.shipping, products.price, inventoryprofile.estshipcost, InventoryProfile.MAPPricingAmount)
Avatar of Bobby

ASKER

Thanks you guys. Had to bite the bullet and do it "right". Good advice.
@QMBB,

Good choice. Although one might think function call (haven't used so far) will take more time, but it puts you in control by ease of debugging and avoiding endless back and forth with the experts.

Mike
Avatar of Bobby

ASKER

Agreed. Thanks again.
and a year from now, when you go back to edit that query, and cannot for the life of you figure out what a syntax that looks like:

CalcField: Round(20 * iif(SomeField = SomeVAlue, iif(someotherfield = SomeOtherValue, CalcVal1, CalcVal2), iif(ThirdField = ThirdValue, CalcVal3, CalcVal4)))/20

Open in new window


is supposed to do.