Hi! New guy here and would greatly appreciate you collective knowledge. I'm creating a Point Of Sale. The subform is based on a query that allows me to chose various products. The subform query determines the amount with the expr amount: [price]*[quantity]. My problem is that not all of the products are taxable, so really I have 2 different expressions:
taxable = ([price]*[quantity])*1.069
or
not taxable = [price]*[quantity]

I'm building the subform from the query because I need to Sum the product amounts on the main form. Can the query use an If Then Else (item.column(3) is a checkbox on the product for tax or no tax):

If (item.column(3) = true) then
amount: ([price]*[quantity])*1.069
else
amount: [price]*[quantity]

Thanks in advance!

No VBA statements in queries. If you really need to use a functon for that in your query, have a look to IIF in Access Help.

Alex

Thanks for the quick response. In the query, I have amount:[price]*[quantity]. Then I put this in the control source of [amount] on the subform:
=IIF ([item].column(4)=True, amount*1.069, amount)
This yields the correct amount on the subform line (Thanks, Alexandre), but I am not getting the correct total on the main form. The main form control [total] calls from the footer of the subform [Sub Total]=Sum([amount]). It's still doing the sum, but not accounting for the values of the IIF in [amount].

IE 1 Item(no tax) @ $20.00 = $20.00
1 Item(1.069) @ $19.95 = $21.33
The sum should be $41.33, but the form returns $39.95 (which is the value from the query 20.00+19.95).

I tried =Sum(Val([amount])), but I get #error

Whaddya think?

Put your IIF statement in your SUM:

=SUM(IIF([item].column(4)=True;amount*1.069; amount))

Greetings,

RV

Thanks RV. I tried it, but I get #error.

Will the IIF work in the query or only on the form?

I think my problem is trying to sum a calculated control.

=SUM(IIF([item].column(4)= -1,([price]*[quantity])*1.069, ,([price]*[quantity]))

There are some situations where you must use -1 and 0 for True and False. This may be one of them.

Thanks Pat! The IIF is working fine, my problem is trying to sum the IIF control. I tried your suggestion just in case, but get the same result #error.