Automating Access form to remove tax when tax "yes/no" field is unchecked

[mulchgirl]

I've been using Access for awhile, although not very familiar with VB. I am designing an order form that automatically calculates tax on the products ordered. On the form and in the underlying table, I have a yes/no field to check if the customer is taxable. I would like the expression I built to calculate tax on the form, to =0 if the yes/no field is unchecked, meaning the customer is nontaxable. Any simple way to do this?
Thanks for any help

[zmbd]

Not really sure if all of your control names and field names.
However the basic would be to use an IIF() in an unbound field.
It will also vary a tad base on the structure of your form

So in the unbound control, in the recordsource property, you'd build your formula. Here's a link to the IIF:
IIf Function

Give-it-whirl, see what you come up with... if you get stuck post back and we'll have a go at it from there!
You can also check out the insight articles

[mulchgirl]

I think I understand, except, the control I am trying to make either show the calculated tax or not, is a calculation. =([qryOrderDetailsExtended subform]![Order Subtotal])*0.06 - it's subtotaling the entended prices and multiplying them by our tax rate, and when I put that calculation in as my positive to if my [taxable] field is yes, I just get ######### in my field on my form. But when I put $0.00 in as my negative, it does work - so I'm not sure if it is still doing the calculation or not.
Thanks again

[mulchgirl]

I should have said that I do have $0.00 in as my false part and when I uncheck the customer's tax status, it does say $0.00, it's just when I check the status as taxable, I get the ######

[Seth Schrock]

If you are just getting ########, then that just means that your control isn't big enough to display your data. You just need to make your control wider.

[mulchgirl]

Thanks Seth, but I had tried adjusting the control size earlier and got the same. The total should be only over $1000.00 and I have the decimal spaces set to 2 - I'm thinking it's got to be in my code.

[zmbd]

@mulchgirl:
For a temporary check...
Please go back in to the design mode of the form.
Please double the width of the control that has the calculation.
You do not need to save it at this point...
Switch from design to form view.
Hopefully you will see the expected result.


@All:
We've started branching on this thread from the original question; however because we haven't yet verified the calculation's result due to the formatting issue, I think it's allowable.
Once mulchgirl has verified the results, the formatting question should be started in a new thread, with reference to this thread if the information is needed for clarity.

[mulchgirl]

zmbd - sorry if I am causing an issue - I expanded the width and what shows in the control is not an answer to my calculation, but just the calculation...... =([qryOrderDetailsExtended subform]![Order Subtotal])*0.06. Do I need to add something to my IIf statment to have it calculate? Thanks so much.

[zmbd]

If you are still in design view, that is expected; hoewever, if you are in Form view then we have an issue with how the formula was entered into the control.

Please confirm what you saw in the Form view.

[mulchgirl]

When I am in form view, it shows the calculation: ([qryOrderDetailsExtended subform]!]Order Subtotal])*0.06 in the control - exactly what I typed in the control in the design view, in the truepart of the IIf function. To help explain more, In my Orders table, I have a field named "Taxable", it's data type is yes/no. I added this field to an "Orders" form (this is based on a query that combines the Orders table and Order Details table). On this form I have a a control that calculates the totals of all the products for the one order (which is the above calculation). My goal is to have a control that calculates tax of 6% on the total fo the products if the customer is taxable. I hope my expanation helps, and doesn't just show how little I understand ;)

[Seth Schrock]

While in design view, you need to enter an equals sign (=) before your calculation.

[mulchgirl]

Seth, I tried adding the = sign before the equation and after the ", in the truepart, and it still shows only the equation itself, not a result of the calculation.

[Seth Schrock]

I'm not seeing the " in your equation, so I'll how the equation should be put into the textbox in design view:

=([qryOrderDetailsExtended subform]!]Order Subtotal])*0.06

[mulchgirl]

Yeah, sorry - the " " were for around my equation in the true part of my IIf statement. My Iff statement now looks like this:

=IIf([Taxable]=Yes, "=[qryOrderDetailsExtended subform]![Order Subtotal]*.06)","$0.00")

. I am getting the result of $0.00 if I uncheck my taxable box (which is what I want), but if my taxable box is checked, all that shows in my control in the form view is =[qryOrderDetailsExtended subform]![Order Subtotal]*.06" - Thanks for all your help!

[zmbd]

Remove the inner double quotes.

=IIf([Taxable]=Yes, ([qryOrderDetailsExtended subform]![Order Subtotal]*.06),"$0.00")

[mulchgirl]

Yes, yes, yes!!!! Thank you very much - it worked. Can I ask why the removal of the " "? And sorry about not using the code button - thanks for you patience - you have been a great help.

[zmbd]

Anything placed between the quotes is taken as a literal string; therefor, treated as a text value to display.

Scroll to the bottom of this page,
Click on the Microsoft Access Insights Site Map Link
Read articles:
174. Literal DateTimes and Their Delimiters (#) and
175. Quotes (') and Double-Quotes (") - Where and When to use them