Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    68

    Unanswered: Should i use =IIF() or =SUM() or Both

    i need to write an expression to place in the control of a textbox on a report. My report sorts by job number. for each job number there is a taxstatus "T" or "T/E". I need to add tax to the totals for each job numer that is taxable.

    This is the code i have now, but it doesnt seem to be working.

    =iif([Items for Job Transfer Query]![Tax Status] = "T", =sum([quantity]*[unit price]*1.06), =sum([quantity]*[unit price]))

    Am i way off base?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Code:
    =iif([Items for Job Transfer Query]![Tax Status] = "T", sum([quantity]*[unit price]*1.06), sum([quantity]*[unit price]))
    Sam

    ps all i did was remove the two extraneous equal signs. BTW, since a job is either taxable or not, why have text (T/TE)? All you need is a field called Taxable, and it could be Boolean (True/False).

  3. #3
    Join Date
    Mar 2012
    Posts
    68
    Quote Originally Posted by Sam Landy View Post
    Code:
    =iif([Items for Job Transfer Query]![Tax Status] = "T", sum([quantity]*[unit price]*1.06), sum([quantity]*[unit price]))
    Sam

    ps all i did was remove the two extraneous equal signs. BTW, since a job is either taxable or not, why have text (T/TE)? All you need is a field called Taxable, and it could be Boolean (True/False).
    Agreed. The table was already created with that arrangment before I started with this business. Thanks for your help.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one thing to bear in mind inside an Access report is that you can also include code. so you don't neccessarly need to do everything in a query. and this is especially useful if the query is getting overly complex. howeve if you are calcuating tax based on soemthgn then its always a smart idea to do that in a query (then the query can be reused elsewhere. there is another reason that code inside a report may not be immediately obvious so there is a risk that when revisiting the report or someone else is looking at the report they could miss that code.

    but VBA code can be very useful for formatting and conditional inclusion of columns


    the classicla way of handling sales tax issues is to have a tax rate table, with dates and have a zero rate rate and alwasy calcualte the tax (even if zero) thsat way round you can adjsut rates as required, its a consistent calcualtion.

    the reason for the tax code with dates is that politicians have a nasty habit of chanigng tax rates as and when they feel like it* so by associating a tax code with a start date you (or your users) can put in the new rates as required without development time and without requiring validation or checking. after all its user data, its their responsibility.


    *or as and when they screw up the economy which ever comes first
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2012
    Posts
    68
    Quote Originally Posted by healdem View Post
    one thing to bear in mind inside an Access report is that you can also include code. so you don't neccessarly need to do everything in a query. and this is especially useful if the query is getting overly complex. howeve if you are calcuating tax based on soemthgn then its always a smart idea to do that in a query (then the query can be reused elsewhere. there is another reason that code inside a report may not be immediately obvious so there is a risk that when revisiting the report or someone else is looking at the report they could miss that code.

    but VBA code can be very useful for formatting and conditional inclusion of columns


    the classicla way of handling sales tax issues is to have a tax rate table, with dates and have a zero rate rate and alwasy calcualte the tax (even if zero) thsat way round you can adjsut rates as required, its a consistent calcualtion.

    the reason for the tax code with dates is that politicians have a nasty habit of chanigng tax rates as and when they feel like it* so by associating a tax code with a start date you (or your users) can put in the new rates as required without development time and without requiring validation or checking. after all its user data, its their responsibility.


    *or as and when they screw up the economy which ever comes first
    So if i set up this table and the expression multiplies the tax of zero, wouldnt the equation = zero

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •