Bobby
asked on
2 IIF's in Access query
I have the following in an Access query and it works fine:
I need to add another If statement... if InventoryProfile.MAPPricin gAmount <> $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.
IIf(bb_stock_status.shipping="1",Round(20*(products.price),0)/20,Round(20*(products.price+inventoryprofile.estshipcost),0)/20)
I need to add another If statement... if InventoryProfile.MAPPricin
So, the final price output will be one of three possibilities.
ASKER
Thanks. Looks like I failed to mention that the final price including the MAP price needs to include InventoryProfile.EstShipCo st, 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.
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.MAPPricin gAmount <> $0.00 or <> Is Null, use the amount in that field as products.price.
I need to add another If statement... if InventoryProfile.MAPPricin
ASKER
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.estshipco st, rounded to the nearest nickle, as the FinalPrice. What I need to do is also see if inventoryprofile.mappricin gamount exists (anything other than null or $0.00), and if it does, use that amount + inventoryprofile.estshipco st as the FinalPrice.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Change this:
iif(Profile.MAPPricingAmou nt <> $0.00 or nz(Profile.MAPPricingAmoun t,'')='', ....
To this:
iif(nz(Profile.MAPPricingA mount,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().
iif(Profile.MAPPricingAmou
To this:
iif(nz(Profile.MAPPricingA
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().
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?
ASKER
just so we're clear, that table is called InventoryProfile, not just Profile.
ASKER
Mike,
Is what you're doing rounding to the nearest nickle? Doesn't look like it just viewing the code...
Is what you're doing rounding to the nearest nickle? Doesn't look like it just viewing the code...
ASKER
Pat,
InventoryProfile.MAPPricin gAmount is kinda screwy as far as table design... it either has a real dollar value, a $0.00, or it's null.
InventoryProfile.MAPPricin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.MAPPricinThere 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.gAmount is kinda screwy as far as table design... it either has a real dollar value, a $0.00, or it's null
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.
ASKER
Thanks Pat. Can you please provide a finished example of what you suggest?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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_stat us.shippin g, products.price, inventoryprofile.estshipco st, InventoryProfile.MAPPricin gAmount)
"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_stat
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
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
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:
is supposed to do.
CalcField: Round(20 * iif(SomeField = SomeVAlue, iif(someotherfield = SomeOtherValue, CalcVal1, CalcVal2), iif(ThirdField = ThirdValue, CalcVal3, CalcVal4)))/20
is supposed to do.
Open in new window