Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to use an IF Statement in Access

Discussion in 'Information Technology' started by Z, Jul 28, 2006.

  1. Z

    Z
    Expand Collapse
    Guest

    Hi, The question I have is how to use an IF Statement with Access.
    a. Using access I created a table made of 3 columns
    1. ItemNum
    2. Cost
    3. Quantity
    From this table I created a Query with these additional fields
    1. SubTotal: [Quantity]*[ItemCost]
    2. Tax: [SubTotal]*0.0825
    3. Total: [SubTotal]+[Tax]
    From this Query I create a Form – on this form I place a Radio button – If
    the Radio button is NOT checked, the item is taxable – Then add tax to the
    total. - If it is check, the item is not taxable, do not add tax.
    That is it.

    I am open to any suggestion such as using Radio button or check box, or …
    In the query which develops the form and report, I do the calculation.

    Please advice,
    Tony
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Hi Tony

    If some items are taxable, and others are not, I suggest you add another
    column to your able:
    4. TaxRate
    The field type will be Number, size Double, format Percent.

    Now add it as a text box in your form.
    Set its Default Value to 0.0825
    For non-taxable items, enter zero in that row.

    In query design, type this expression into the Field row:
    Tax: CCur(Round(Nz([Quantity] * [ItemCost] * [TaxRate],0),2))

    By storing the tax rate instead of a yes/no (radio button), your design not
    only copes with tax-free items, but will also work correctly for all
    existing records when they change the tax rate at some time in the future,
    or if the software ends up being used in a place that has a different tax
    rate.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Z" <Z@discussions.microsoft.com> wrote in message
    news:018438F6-373A-446A-843B-E9038A26FF02@microsoft.com...
    > Hi, The question I have is how to use an IF Statement with Access.
    > a. Using access I created a table made of 3 columns
    > 1. ItemNum
    > 2. Cost
    > 3. Quantity
    > From this table I created a Query with these additional fields
    > 1. SubTotal: [Quantity]*[ItemCost]
    > 2. Tax: [SubTotal]*0.0825
    > 3. Total: [SubTotal]+[Tax]
    > From this Query I create a Form - on this form I place a Radio button - If
    > the Radio button is NOT checked, the item is taxable - Then add tax to the
    > total. - If it is check, the item is not taxable, do not add tax.
    > That is it.
    >
    > I am open to any suggestion such as using Radio button or check box, or .
    > In the query which develops the form and report, I do the calculation.
    >
    > Please advice,
    > Tony
     

Share This Page