IIF Expression

[irishcavan]

I am trying to execute a query that calculates a retail pricing off the whsl pricing. This is the excel calculation i use: =IF(F2<10,(SUM(F2*4)),(SUM(F2*2))). I changed the IF to IIF, and replaced "F2" with "[WHSL PRICE].

However, I am getting an error that the "category" expression is not included in the function (when I have all the fields in the query) or the function if I have just the field I am trying to calculate in the query. Can somone help me understand what I am doing wrong.

I would also like this calculation to save in the table, if possible. As I use this information to update my fields in my web database.


Best Regards,

Irish

 

[Sammy B]

As long as you actually have a field called "WHSL Price” then just go to the first empty query cell and type in the top row:

Mycalc:sum(iif([WHSL PRICE]<10,[WHSL PRICE]*4,[WHSL PRICE]*2))

(I did not actually check to see if this works. I am going on memory and at time that fails me too!!)

Sam

 

[Sammy B]

You may need an "=" sign after MyCalc:

(Can not remember)

 

[irishcavan]

Sammy, thanks for the advice. However, (cry, cry)... I tried it with and without the "=", and both times i still get the error msg:

"You tried to execute a query that does not include the specified expression 'v_categories_name_1' as part of an aggregate function. (same msg as before)

Here are the specs for the query, if that will help at all to solve this problem.

Tbl #1:
Product Inventory Basics // Main Tbl
Fields:
v_categories_name_1
v_categories_name_2
v_products_model // linked to tbl #2
v_products_name_1
v_products_description_1
v_products_price
v_products_quantity
ETA
Music
Dimensions
Unit of Sale
Updated
In Stock

Tbl #2:
Product Inventory Upgrate
Fields:
v_products_model
v_products_name_1
WHSL PRICE
SUGG RETAIL PRICE
UNIT OF SALE
v_products_quantity
ETA
Discontinued
Updated

Query //Make table Query

Fields / Tbl:
v_categories_name_1 / Basics
v_categories_name_2 / Basics
v_product_model / Basics
v_products_name_1 / Basics
v_products_description_1 / Basics
v_product_price / Basics // this is the field that i need the calculation in
Dimensions / Update
WHSL PRICE / Update
SUGG RETAIL PRICE / Update
UNIT OF SALE / Update
v_products_quantity / Update
ETA / Update
Discontinued / Update
Updated / Update

Thanks,

Irish

 

[Sammy B]

I aploogize. When I went in to try it I remebered that it is not that simple. You must click on the sum symbol on the top menu (looks like a "M" on its side). A new row called the Total row will appear. Type in the same expression in the MyCalc: I mentioned earlier and then click on the Totals row and choose "Expression"..
Leave the other cells on the default 'Group By' setting.

This should work

Sam

 

[irishcavan]

Thanks, Sammy, that worked.

One last question. The final 2 steps I took in calculating the Retail Price was SUM(ROUNDUP([RETAIL PRICE],-0.1)-0.05).

Thanks,

Irish

 

[Sammy B]

You said:

One last question. The final 2 steps I took in calculating the Retail Price was SUM(ROUNDUP([RETAIL PRICE],-0.1)-0.05).

I did not understand the question?

 

[irishcavan]

Sorry, Sammy... the question was about incorporating the remaining part of the calculation into the expression.

Basically:

Whsl Price: $9.95
Retail Price: $39.95 (right now my total shows $39.80)

Orange being the current expression:

Sum(roundup((IIf([WHITE PRICE]<10,[WHITE PRICE]*4,[WHITE PRICE]*2)),-0.01)-0.05)

Breakdown: 9.95 * 4 = 39.80
39.80 rounds up to 40.00
Subtract 40.00 - 0.05 - 39.95

Thanks,

Irish

 

[irishcavan]

Also, since you are being a wonderful help.. I would like to be able to actually use this formula in my form. Example: I enter in a record, put in the whsl price and it automatically calculates what the retail price would be and stores it in my table. Sorry for such basic questions. It has been a while since I have played around in Access, so it's all coming back slowly - but any help I can get would be IMMENSELY appreciated.

Thanks,

Irish

 

[Sammy B]

If you are asking if it is possible to add in the rest of your expression, I do not see why not.

Are you saying you tried it and it did not work?

Sam

 

[irishcavan]

Hey, Sammy. Yes.. I tried the remaining part of the expression and "ROUNDUP" is not recognized by Access.. I am trying to find the function that I need to placed "ROUNDUP". Once I can replace that part, I can finish the expression.

Thanks,

Irish

 

[irishcavan]

Hey, Sammy. I got it.. I needed to replace ROUNDUP with INT,

Old Expression:
Sum(roundup((IIf([WHITE PRICE]<10,[WHITE PRICE]*4,[WHITE PRICE]*2)),-0.01)-0.05)

New Expression:
(Int(IIf([WHITE PRICE]<10,[WHITE PRICE]*4,[WHITE PRICE]*2))+1)-0.05

Thanks for all your help Sammy.

Irish