If Statement in Subform

  • Thread starter Thread starter Sherry
  • Start date Start date
S

Sherry

I am trying to write a formula in a subform. The basics of
the formula are, if a Yes/No field is Yes, add tax, if the
Yes/No field is No, add 0.

I have these fields:

[Amount](This field data is input by the user)
[Taxable Item](This is a Yes/No field, determined by a
checkmark.

[If Taxable Item, Add Tax] (Formula in this field
reads: "IIf[Taxable Item?]=Yes, [Taxable Item]*.07" This
then returns what the tax amount would be.)

[Total Amount] )Formula in this field reads "[Taxable
Item?]+[If Taxable Item, Add Tax]")

This works like a charm if the Taxable Item field is
checked yes. If it is not checked, it will not return any
value in the If Taxable Item, Add Tax field or the Total
Amount field, and will not allow manual entries into
either of those fields.

Yes, I know my formula is not complete, but I have tried
to complete it as follows:

IIf[Taxable Item?]=Yes,[Amount]*.07, IIf[Taxable Item?]
=No, 0)

I am certain what I want to do here will work, if only I
can find the correct syntax. Can anybody please help me???
I need to have this project completed by the end of next
week.

If you think you can help, please feel free to email me
for more questions! Thanks! Thanks! Thanks!
 
based on the formulas you give, i'm unclear on your field usage. this is
what i think you mean:
[Amount] = the dollar amount of the item, entered by the user.
[Taxable Item] = a Yes/No field to determine whether or not to apply a tax.
i assume that [Taxable Item?] is actually the same field, though your code
is confusing on this point.

if the above is correct, suggest you try the following formulas.

[If Taxable Item, Add Tax] formula:
IIf([TaxableItem] = Yes, [Amount] * .07, 0)

[Total Amount] formula:
[Amount] + [If Taxable Item, Add Tax]

hth
 
Sherry,

The correct format for IIF is IIF(condition,true statement,false statement).
So your formula should read

IIf([Taxable Item?]=Yes,[Amount]*.07, 0)

Kelvin
 
Thanks Kevin and Tina. This worked perfectly. As you can
probably tell, I'm a newbie at this. No training in Access
whatsoever, so I'm just having to wing it.
 
you can teach yourself a lot in Access, as long as you're willing and able
to put in the tons of time it takes (voice of experience, here). a good
reference like the Microsoft Access Bible - i believe there's an edition for
each version of Access - can help a lot.
one area i strongly recommend you get assistance with is relational table
design, unless you have prior training/experience. the biggest mistakes
newbies commonly make are in poor table/relationship design, and those
mistakes cause problems and headaches throughout the rest of the db.
one book i can recommend is Database Design for Mere Mortals by Michael J
Hernandez. it was the chosen textbook for a class i took to learn data
modeling (table/relationship design). the time/money you would invest in
taking a data modeling class, would repay you 1,000-fold in better - meaning
easier - db design.
also suggest you read the tips on db design at
http://www.mvps.org/access/tencommandments.htm
and also see what other good help and advice that website will give you.

hth


Thanks Kevin and Tina. This worked perfectly. As you can
probably tell, I'm a newbie at this. No training in Access
whatsoever, so I'm just having to wing it.
-----Original Message-----
I am trying to write a formula in a subform. The basics of
the formula are, if a Yes/No field is Yes, add tax, if the
Yes/No field is No, add 0.

I have these fields:

[Amount](This field data is input by the user)
[Taxable Item](This is a Yes/No field, determined by a
checkmark.

[If Taxable Item, Add Tax] (Formula in this field
reads: "IIf[Taxable Item?]=Yes, [Taxable Item]*.07" This
then returns what the tax amount would be.)

[Total Amount] )Formula in this field reads "[Taxable
Item?]+[If Taxable Item, Add Tax]")

This works like a charm if the Taxable Item field is
checked yes. If it is not checked, it will not return any
value in the If Taxable Item, Add Tax field or the Total
Amount field, and will not allow manual entries into
either of those fields.

Yes, I know my formula is not complete, but I have tried
to complete it as follows:

IIf[Taxable Item?]=Yes,[Amount]*.07, IIf[Taxable Item?]
=No, 0)

I am certain what I want to do here will work, if only I
can find the correct syntax. Can anybody please help me???
I need to have this project completed by the end of next
week.

If you think you can help, please feel free to email me
for more questions! Thanks! Thanks! Thanks!


.
Click to expand...
Click to expand...
 
Back
Top