IIF Statement Advice

[BinHDreW]

Hi All,



I am trying to figure something else, but I'm all lost for ideas.

I have a form with three criteria

• Cost per attendee
• Cost of venue
• Additional costs

what i have set up is I've made 3 tick boxes adjacent to the form files called:

• gst 1
• gst 2
• gst 3

What i want to do it calculate the interest (10%) on each criteria

these are the examples if had for the first criteria:

• =IIF([Cost per attendee]*1.1, [GST1]="TRUE", [GST1]="FALSE"
• =IIF([GST1]="TRUE",[Cost per attendee]=[Cost per attendee]*1.1)

can anyone give me some advice?

thanks

[June7]

Your IIF expressions are incomplete and don't make sense. Actually, none of this makes sense. You have check boxes 'adjacent to the form files' - what are form files? Calculating 10% is simple. What I don't understand is what role do the check boxes play?

[BinHDreW]

Hi June,

i should have been more specific and uploaded a picture.

under financial details, id like too add a 10% tax on the cost type (cost per attendee).

quite new to Access, i've only used it to make simple queries.

Thanks

[June7]

You want to calculate Additional Cost? You do know that calculated values will not be saved to table without use of code?

Your example shows $20, but 10% of $100 would be $10. Was this a manual entry or do you have an expression in the textbox ControlSource? How should the check boxes be considered in the cost calculation?

[BinHDreW]

Thanks for getting back, I want each corresponding GST tick box to calculate 10% on the manually entered costs. So in "hoping" that this is possible, i wanted the GST (GST1) to be ticked and add the percentage.

Wouldn't a formula using a multiplier of 1.1 add the 10%?

Yeah im aware that i wont be able to save the calculated vales. Im searching google for a way around that, if there is one ahahah

[June7]

So for each GST checkbox, add on another 10%? Your image does not show any checkboxes ticked but $20 is in the Additional Cost field. How did that happen? You need to clearly explain the behavior this form and its controls should exhibit and the formula to use. Without that, can't be of any help.

Relational database principal is save raw data, calculate in reports, and that is the way Access is designed. Having said that, I often encounter a requirement to save a calculated value. This will require VBA code in some event (form Close, button Click, textbox AfterUpdate, etc). If the field value needs to be saved to is in the form RecordSource, could simply be: Me!fieldname = Me.textboxname.

[BinHDreW]

Okay, let me take a step back first.

there are 3 elements that contribute to the total cost: Cost p attendee + cost of venue and additional cost. So that's 3 separate costs involved. So if we're working on this example for argument sake, assuming that i've ticked gst for cost p atendee and cost of venue, i should get $110 and $550.

the reason for the tick boxes, is because GST tax is an automatic 10%, but i want to give the user a choice of including the tax.

[June7]

Okay, maybe I have it. Additional Cost is not the calculated tax but another cost that could be taxed. Each GST checkbox determines whether tax is applied to that cost.

If you have another textbox to calculate and display the tax, the expression would be like:
=IIf(GST1=True,[Cost per attendee]*.10,0) + IIf(GST2=True,[Cost of Venue]*.10,0) + IIf(GST3=True,[Additional Cost]*.10,0)

[BinHDreW]

That's fantastic June! Works like a charm.

thanks for the advice.