Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $95

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
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page