Iff statement

[Guest]

Hello.
I have a continous form with the following fields:
SaleAmount
Discount
Taxable? (Yes/No check mark field)

I am trying to create a field that will calculate the tax for each record
i.e if "Taxable?" is checked than SaleAmount-Discount*.075 else 0

This is what I wrote and it doesn't work:
IIf(([Taxable?])="Yes", ([SaleAmount])-([Discount])*.075, 0))

How should I write it?

Thanks your help is greatly appreciated.
Iram/mcp

 

[Arvin Meyer [MVP]]

Try:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)

 

[Guest]

Avoid using the IIf statement in VBA. It is slower than an If Then Else
because it evaluates both the true and false parts regardless of which will
be used. It is fine for queries, but in VBA the If Then Else is better.
I would suggest you write a sub and call it from the After Update event of
both the SalesAmount and Discount controls.

Private Sub CalcTaxAmount()
If Me.Taxable Then
Me.TaxAmount = Nz((Me.SaleAmount,0) - Nz([Discount],0))*.075
End If
End Function

And to use it:

Private Sub SalesAmount_AfterUpdate()
Call CalcTaxAmount
End Sub

 

[Guest]

I have tried the following and all I get is #Name? in form view:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
IIf([Taxable?] = -1, 1, 0)
IIf([Taxable?] = Yes, 1, 0)
IIf([Taxable?] = "-1", 1, 0)
IIf([Taxable?] = "Yes", 1, 0)

Would it matter if I just created the text box and put it in the reoccuring
form or does it have to be linked to a table?

Iram/mcp

Try:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello.
I have a continous form with the following fields:
SaleAmount
Discount
Taxable? (Yes/No check mark field)

I am trying to create a field that will calculate the tax for each record
i.e if "Taxable?" is checked than SaleAmount-Discount*.075 else 0

This is what I wrote and it doesn't work:
IIf(([Taxable?])="Yes", ([SaleAmount])-([Discount])*.075, 0))

How should I write it?

Thanks your help is greatly appreciated.
Iram/mcp

 

[Guest]

If you are using your code in a control property (Default Value, Control
Source) then you need to preceed it with =

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0) or
IIf([Taxable?] = True, ([SaleAmount]-[Discount])*0.75, 0)

--
Dave Hargis, Microsoft Access MVP

I have tried the following and all I get is #Name? in form view:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
IIf([Taxable?] = -1, 1, 0)
IIf([Taxable?] = Yes, 1, 0)
IIf([Taxable?] = "-1", 1, 0)
IIf([Taxable?] = "Yes", 1, 0)

Would it matter if I just created the text box and put it in the reoccuring
form or does it have to be linked to a table?

Iram/mcp

Try:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello.
I have a continous form with the following fields:
SaleAmount
Discount
Taxable? (Yes/No check mark field)

I am trying to create a field that will calculate the tax for each record
i.e if "Taxable?" is checked than SaleAmount-Discount*.075 else 0

This is what I wrote and it doesn't work:
IIf(([Taxable?])="Yes", ([SaleAmount])-([Discount])*.075, 0))

How should I write it?

Thanks your help is greatly appreciated.
Iram/mcp

 

[Guest]

An amount appears in my "CalcTaxAmount" field when I put the first suggestion
in to the Default Value.The amount doesn't appear to be correct and seems to
replicate the same amount for all fields.

When I put the first suggestion into the Control Source I get the same error
#Name? in form view.

I can't figure out what I am doing wrong.
Your help is greatly appreciated.

Iram/mcp

If you are using your code in a control property (Default Value, Control
Source) then you need to preceed it with =

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0) or
IIf([Taxable?] = True, ([SaleAmount]-[Discount])*0.75, 0)

--
Dave Hargis, Microsoft Access MVP

I have tried the following and all I get is #Name? in form view:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
IIf([Taxable?] = -1, 1, 0)
IIf([Taxable?] = Yes, 1, 0)
IIf([Taxable?] = "-1", 1, 0)
IIf([Taxable?] = "Yes", 1, 0)

Would it matter if I just created the text box and put it in the reoccuring
form or does it have to be linked to a table?

Iram/mcp

Try:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello.
I have a continous form with the following fields:
SaleAmount
Discount
Taxable? (Yes/No check mark field)

I am trying to create a field that will calculate the tax for each record
i.e if "Taxable?" is checked than SaleAmount-Discount*.075 else 0

This is what I wrote and it doesn't work:
IIf(([Taxable?])="Yes", ([SaleAmount])-([Discount])*.075, 0))

How should I write it?

Thanks your help is greatly appreciated.
Iram/mcp

 

[Guest]

What do you mean by "all fields"?
Is this a datasheet or continuous form?
--
Dave Hargis, Microsoft Access MVP

An amount appears in my "CalcTaxAmount" field when I put the first suggestion
in to the Default Value.The amount doesn't appear to be correct and seems to
replicate the same amount for all fields.

When I put the first suggestion into the Control Source I get the same error
#Name? in form view.

I can't figure out what I am doing wrong.
Your help is greatly appreciated.

Iram/mcp

If you are using your code in a control property (Default Value, Control
Source) then you need to preceed it with =

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0) or
IIf([Taxable?] = True, ([SaleAmount]-[Discount])*0.75, 0)

--
Dave Hargis, Microsoft Access MVP

I have tried the following and all I get is #Name? in form view:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
IIf([Taxable?] = -1, 1, 0)
IIf([Taxable?] = Yes, 1, 0)
IIf([Taxable?] = "-1", 1, 0)
IIf([Taxable?] = "Yes", 1, 0)

Would it matter if I just created the text box and put it in the reoccuring
form or does it have to be linked to a table?

Iram/mcp

:

Try:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello.
I have a continous form with the following fields:
SaleAmount
Discount
Taxable? (Yes/No check mark field)

I am trying to create a field that will calculate the tax for each record
i.e if "Taxable?" is checked than SaleAmount-Discount*.075 else 0

This is what I wrote and it doesn't work:
IIf(([Taxable?])="Yes", ([SaleAmount])-([Discount])*.075, 0))

How should I write it?

Thanks your help is greatly appreciated.
Iram/mcp

 

[Guest]

I'm sorry I meant the same amount appears in the "CalcTaxAmount" field of
each record on the continuous form. There is only one "CalcTaxAmount" field
in each record. I am trying to calculate the tax for each sale record on the
same line. Again this is with your first Iff statement in the Default Value
of the "CalcTaxAmount" field.

Thanks.
Iram/mcp

What do you mean by "all fields"?
Is this a datasheet or continuous form?
--
Dave Hargis, Microsoft Access MVP

An amount appears in my "CalcTaxAmount" field when I put the first suggestion
in to the Default Value.The amount doesn't appear to be correct and seems to
replicate the same amount for all fields.

When I put the first suggestion into the Control Source I get the same error
#Name? in form view.

I can't figure out what I am doing wrong.
Your help is greatly appreciated.

Iram/mcp

If you are using your code in a control property (Default Value, Control
Source) then you need to preceed it with =
IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
or
IIf([Taxable?] = True, ([SaleAmount]-[Discount])*0.75, 0)
--
Dave Hargis, Microsoft Access MVP


:

I have tried the following and all I get is #Name? in form view:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
IIf([Taxable?] = -1, 1, 0)
IIf([Taxable?] = Yes, 1, 0)
IIf([Taxable?] = "-1", 1, 0)
IIf([Taxable?] = "Yes", 1, 0)

Would it matter if I just created the text box and put it in the reoccuring
form or does it have to be linked to a table?

Iram/mcp

:

Try:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello.
I have a continous form with the following fields:
SaleAmount
Discount
Taxable? (Yes/No check mark field)

I am trying to create a field that will calculate the tax for each record
i.e if "Taxable?" is checked than SaleAmount-Discount*.075 else 0

This is what I wrote and it doesn't work:
IIf(([Taxable?])="Yes", ([SaleAmount])-([Discount])*.075, 0))

How should I write it?

Thanks your help is greatly appreciated.
Iram/mcp

 

[Guest]

Sorry. I found the problem. I added an = before the IIF and it worked.
I appreciate your help very much.
Thanks for your help.

Iram/mcp

If you are using your code in a control property (Default Value, Control
Source) then you need to preceed it with =

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0) or
IIf([Taxable?] = True, ([SaleAmount]-[Discount])*0.75, 0)

--
Dave Hargis, Microsoft Access MVP

I have tried the following and all I get is #Name? in form view:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
IIf([Taxable?] = -1, 1, 0)
IIf([Taxable?] = Yes, 1, 0)
IIf([Taxable?] = "-1", 1, 0)
IIf([Taxable?] = "Yes", 1, 0)

Would it matter if I just created the text box and put it in the reoccuring
form or does it have to be linked to a table?

Iram/mcp

Try:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello.
I have a continous form with the following fields:
SaleAmount
Discount
Taxable? (Yes/No check mark field)

I am trying to create a field that will calculate the tax for each record
i.e if "Taxable?" is checked than SaleAmount-Discount*.075 else 0

This is what I wrote and it doesn't work:
IIf(([Taxable?])="Yes", ([SaleAmount])-([Discount])*.075, 0))

How should I write it?

Thanks your help is greatly appreciated.
Iram/mcp

 

[Guest]

What you may not know about datasheet and continuous forms is that there
really is only one instance of each control, so when you use a propertry of a
control, it will affect every row. I suggest you try my other suggestion
using a function. That way, when the code executes, it will affect only the
current row.
--
Dave Hargis, Microsoft Access MVP

I'm sorry I meant the same amount appears in the "CalcTaxAmount" field of
each record on the continuous form. There is only one "CalcTaxAmount" field
in each record. I am trying to calculate the tax for each sale record on the
same line. Again this is with your first Iff statement in the Default Value
of the "CalcTaxAmount" field.

Thanks.
Iram/mcp

What do you mean by "all fields"?
Is this a datasheet or continuous form?
--
Dave Hargis, Microsoft Access MVP

An amount appears in my "CalcTaxAmount" field when I put the first suggestion
in to the Default Value.The amount doesn't appear to be correct and seems to
replicate the same amount for all fields.

When I put the first suggestion into the Control Source I get the same error
#Name? in form view.

I can't figure out what I am doing wrong.
Your help is greatly appreciated.

Iram/mcp

:

If you are using your code in a control property (Default Value, Control
Source) then you need to preceed it with =
IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
or
IIf([Taxable?] = True, ([SaleAmount]-[Discount])*0.75, 0)
--
Dave Hargis, Microsoft Access MVP


:

I have tried the following and all I get is #Name? in form view:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
IIf([Taxable?] = -1, 1, 0)
IIf([Taxable?] = Yes, 1, 0)
IIf([Taxable?] = "-1", 1, 0)
IIf([Taxable?] = "Yes", 1, 0)

Would it matter if I just created the text box and put it in the reoccuring
form or does it have to be linked to a table?

Iram/mcp

:

Try:

IIf([Taxable?] = -1, ([SaleAmount]-[Discount])*0.75, 0)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello.
I have a continous form with the following fields:
SaleAmount
Discount
Taxable? (Yes/No check mark field)

I am trying to create a field that will calculate the tax for each record
i.e if "Taxable?" is checked than SaleAmount-Discount*.075 else 0

This is what I wrote and it doesn't work:
IIf(([Taxable?])="Yes", ([SaleAmount])-([Discount])*.075, 0))

How should I write it?

Thanks your help is greatly appreciated.
Iram/mcp