Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Lightbulb Unanswered: Field Calculation based on Conditions

    Hi all,

    I've got small issue with a calculation on an Access module I am working on a the moment.

    The layout is something along the lines

    Cost / RetailPrice / Qty / Tax1 / Tax2

    Tax1 and Tax2 are Yes/No fields

    Basically I need to multiply
    RetailPrice by Qty by 1.1 if Tax1 is Yes or by 1.2 if Tax2 is Yes

    Also some fields may not have either Tax1 or Tax2 ticked.

    I can do the first part as in "Value: [RetailPrice]*[Qty]" but setting the criteria for the additional maths is proving frustrating at the moment.

    I hope I have explained the problem clearly. Any and all help, advice or pointers greatly appreciated.

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    Try this:
    Iif([Tax1]=True,([RetailPrice]*1.1),(Iif([Tax2]=True,([RetailPrice]*1.2),[RetailPrice])))

  3. #3
    Join Date
    Nov 2011
    Posts
    4

    Question

    Thank you, will give it a try in the morning, and let you know how it goes.

    Just one more quick question, I presume I put that in to the sql code after the WHERE statement?

    Cheers

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    I'm an SQL idiot but I did a mockup and here's how it worked

    SELECT Table1.RetailPrice, Table1.Tax1, Table1.Tax2, IIf([Tax1]=True,([RetailPrice]*1.1),(IIf([Tax2]=True,([RetailPrice]*1.2),[RetailPrice]))) AS test
    FROM Table1;

    I named a new field "Test" to return your calculation.

  5. #5
    Join Date
    Nov 2011
    Posts
    4

    Thumbs up

    Thank you chris, just put this to the test and it appears to be exactly what I need.

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    What about putting 1.1 in tax1 and 1.2 in tax2
    Because if the tax changes you don't have to change the formula

    Just thinking ahead
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Nov 2011
    Posts
    4
    Hi myle, the db I'm working with is the back end of an ePOS system, and the tax rates are actually stored in another table, called TaxRates.

    The Tax1 and Tax2 Fields in the table I'm linking to and running the query from are Yes/No Fields, and in the VB Frontend the calculations are run automatically, and the values are stored in the Receipts table.

    Do you think it would be possible to call in the Tax1 and Tax2 fields from the TaxRates table and have the calculation run based on their values?

    If so, can you please give me an example.

    There is no rush in this as chris's post has resolved the issue for now, but I'm always interested in learning a bit more.

    Thanking you.

  8. #8
    Join Date
    Oct 2009
    Posts
    204
    Try This

    SELECT Table1.RetailPrice, Table1.Tax1, Table1.Tax2, IIf([Table1].[Tax1]=True,([RetailPrice]*[TaxRates].[Tax1]),(IIf([Table1].[Tax2]=True,([RetailPrice]*[TaxRates].[Tax2]),[RetailPrice]))) AS Total
    FROM Table1, TaxRates;

Posting Permissions

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