What is wrong with my IIF expression?

[joe_madeira]

Hey. In a subform I have a Final Price text box which I added while in design view. First I had the expression: [Quantity]*[Menu Price]. But then i remembered that every time a customer orders an item more than once the price of the item gets cut down to half the price.

Obviously i need an IIF function. I came up with this one, but it doesnt work. Microsoft Access says "THE EXPRESSION YOU ENTERED CONTAINS INVALID SYNTAX."

= IIf ( [Quantity] = >1 , [Final Price] = [Menu Price]*0.5* [Quantity] , [Final Price] = [Menu Price]* [Quantity] )

Any one have any ideas?

 

[Galaxiom]

= IIf ( [Quantity] = >1 , [Menu Price]*0.5* [Quantity] , [Menu Price]* [Quantity] )

Or in a query

[Final Price]: IIf ( [Quantity] = >1 , [Menu Price]*0.5* [Quantity] , [Menu Price]* [Quantity] )

 

[joe_madeira]

Appreciate your help but it still says error
it says "you may have entered an operand without an operator"
?

 

[Galaxiom]

Take out the equal sign. Sorry I didn't check the detail just the structure.

= IIf ( [Quantity] >1 , [Menu Price]*0.5* [Quantity] , [Menu Price]* [Quantity] )

 

[Never Hide]

Where did you use this IIF? Is it in an event of the the Final_Price_textbox which is in the subform?
Is it in the event of some other control in your Main form?
These [Quantity],[Menu Price] fields you are using, are they in the same form with the control in which you have used the expression?

 

[joe_madeira]

Thanks that worked! thanks!

But hey i think i got the whole thing wrong though.
Basically the rule is:
a customer buys a flavor full price. If they buy that flavor again then the second time round the price is half. how would that be put into an expression?
Any ideas?

 

[joe_madeira]

Where did you use this IIF? Is it in an event of the the Final_Price_textbox which is in the subform?
Is it in the event of some other control in your Main form?
These [Quantity],[Menu Price] fields you are using, are they in the same form with the control in which you have used the expression?

It was in the control source of the text box in design view in a subquery.
got it working tho. however read my post just above this one

 

[Never Hide]

Ooops
My net must have screwed up-or something. There was nothing showing when I replied

Oh well

 

[missinglinq]

...Basically the rule is:

a customer buys a flavor full price. If they buy that flavor again then the second time round the price is half...

That's what I thought you meant:

= IIf ( [Quantity] = >1 , (([Menu Price]*0.5) * [Quantity]) + ([Menu Price]*0.5), [Menu Price]* [Quantity] )

I probably used more parens than necessary, but I can never remember the order of operations so I always err on the side of safety.

Linq ;0)>

 

[joe_madeira]

nop that isn't the correct expression cause the final values are wrong

basically:
If i buy one item the total cost is £10
however if i buy 2 of that item the total cost is £15 (the same item is discounted 50%)
if i buy three of that item the total cost is £20
If i buy four then the total cost is £25...etc

Anyone have any ideas on how to put this into an expression?

 

[joe_madeira]

Ohh I got it!

=IIf([Quantity]<1,[Menu Price]*0.5*[Quantity]+5,[Menu Price]*[Quantity])

+5 to gain the £5 lost in the original £10 of the first item

thanks for all your help!

 

[vbaInet]

-->
[Menu Price]*0.5*[Quantity]+5

Is Menu Price multiplied by 0.5 or is 0.5 multiplying Quantity?
Are you adding 5 to Quantity or to [Menu Price]*0.5*[Quantity]?

Notice how missinlinq properly grouped his calculations using parentheses? You should employ the same technique.

 

[joe_madeira]

thanks for your help but i've got it to work using this:

IIf([Menu Category]="First Floor" And [Quantity]>1,[Menu Price]*0.5*[Quantity]+5,[Menu Price]*[Quantity])

I just had the greater than sign ">" the wrong way around lol. my bad but thanks G

 

[vbaInet]

You still need to put in proper parentheses. Your calculation will fail in some cases.

 

[joe_madeira]

I get what your saying, thanks man!

 

[vbaInet]

No problemo dude!

 

[Brianwarnock]

Surely the menu price is not always £10, shouldn't you add back
In 0.5*menuprice
Or menuprice + menuprice*(quantity-1)*0.5
Which of course always works even if you only buy 1 no check needed

Brian

 

[joe_madeira]

The menu price is always £10 for the menu item that is categorized in the "first floor" category because that category consists of shisha pipe flavours which are all priced at £10. there are other menu items but they are not categorized in "first floor".

 

[Brianwarnock]

Then why the fuss the cost is
10+5*(quantity-1)
In all cases

Brian

 

[MSAccessRookie]

When I read the requirements, I devised a different formula. Will the following work for you?

IIf([Quantity] <= 1, ([Menu Price]*[Quantity]), ([Menu Price]+(([Menu Price]*0.5)*([Quantity]-1)))