Specify "if" statement in control source property of calc field?

[Tec92407]

I have a main form with a subform that contains line items. Each line item
has a "Tax" field that contains Yes or No to indicate if tax should be
applied to the item cost. In the main form I have a calculated Tax field that
contains

=[PRItems Subform].Form!ItemSubTotal*0.0775

in its control source property. This works and computes the total tax for
all items. But I need it to include the item in its computation only if the
line item's tax field contains "Yes".

Is there a way to specifiy a conditional logic statement in the control
source that would only include the line item if the tax field is "Yes"?

 

[Ofer]

Try this using IIf, and I assume that the Tax field name is Tax, else
change it to the fied name


=[PRItems Subform].Form!ItemSubTotal*IIf([PRItems Subform].Form!Tax = True ,
0.0775, 1)

 

[John Vinson]

I have a main form with a subform that contains line items. Each line item
has a "Tax" field that contains Yes or No to indicate if tax should be
applied to the item cost. In the main form I have a calculated Tax field that
contains

=[PRItems Subform].Form!ItemSubTotal*0.0775

in its control source property. This works and computes the total tax for
all items. But I need it to include the item in its computation only if the
line item's tax field contains "Yes".

Is there a way to specifiy a conditional logic statement in the control
source that would only include the line item if the tax field is "Yes"?

The "Immediate If" function IIF will do the trick here:

=IIF([Taxable], [PRItems Subform].Form!ItemSubTotal * 0.0775, 0)

Note that this will not *store* the tax amount anywhere, just display
it - but that is the correct behavior.

One suggestion: tax rates change from time to time. You might want to
consider a) having a tax-rate table to give the current tax rate, and
looking it up in the table rather than hardcoding it in your
expression; and b) actually storing the tax charged in your detail
records to record the taxes as of the time of the sale.

John W. Vinson[MVP]

 

[Tec92407]

Problems....

Here's the expression I'm using:

=[PRItems Subform].Form!ItemSubTotal*IIf([PRItems
Subform].Form!ItemTax="Yes",0.0775,0)

But the results are odd.

1. I have the following two entries in the PRItems Subform
Item Qty Cost ExtCost Tax
1. 1 1.00 1.00 No
2. 2 1.00 2.00 Yes
0 0 0.00 0.00 (empty) <-new record line

2. If I select tax=Yes for any or all items, the computed total tax is
always 0.23

3. The value in the computed tax field always reflects the line Item my
cursor is
placed in. If my cursor is in a line item where the tax field=yes, the total
tax is 0.23. If my cursor is in a line item where the tax field=no, or
tax=(blank), the total tax is 0.00.

Probably something obvious, but, what am I missing?

I have a main form with a subform that contains line items. Each line item
has a "Tax" field that contains Yes or No to indicate if tax should be
applied to the item cost. In the main form I have a calculated Tax field that
contains

=[PRItems Subform].Form!ItemSubTotal*0.0775

in its control source property. This works and computes the total tax for
all items. But I need it to include the item in its computation only if the
line item's tax field contains "Yes".

Is there a way to specifiy a conditional logic statement in the control
source that would only include the line item if the tax field is "Yes"?

The "Immediate If" function IIF will do the trick here:

=IIF([Taxable], [PRItems Subform].Form!ItemSubTotal * 0.0775, 0)

Note that this will not *store* the tax amount anywhere, just display
it - but that is the correct behavior.

One suggestion: tax rates change from time to time. You might want to
consider a) having a tax-rate table to give the current tax rate, and
looking it up in the table rather than hardcoding it in your
expression; and b) actually storing the tax charged in your detail
records to record the taxes as of the time of the sale.

John W. Vinson[MVP]

 

[Tec92407]

More info...

ItemSubTotal is an invisible calculated field in the form footer of the
Items subform. Its purpose is to be able to reference the field values in
each item record to compute the total extended cost of all items. Its control
source contains:

=Sum([ItemQuantity]*[ItemUnitCost])

Problems....

Here's the expression I'm using:

=[PRItems Subform].Form!ItemSubTotal*IIf([PRItems
Subform].Form!ItemTax="Yes",0.0775,0)

But the results are odd.

1. I have the following two entries in the PRItems Subform
Item Qty Cost ExtCost Tax
1. 1 1.00 1.00 No
2. 2 1.00 2.00 Yes
0 0 0.00 0.00 (empty) <-new record line

2. If I select tax=Yes for any or all items, the computed total tax is
always 0.23

3. The value in the computed tax field always reflects the line Item my
cursor is
placed in. If my cursor is in a line item where the tax field=yes, the total
tax is 0.23. If my cursor is in a line item where the tax field=no, or
tax=(blank), the total tax is 0.00.

Probably something obvious, but, what am I missing?

I have a main form with a subform that contains line items. Each line item
has a "Tax" field that contains Yes or No to indicate if tax should be
applied to the item cost. In the main form I have a calculated Tax field that
contains

=[PRItems Subform].Form!ItemSubTotal*0.0775

in its control source property. This works and computes the total tax for
all items. But I need it to include the item in its computation only if the
line item's tax field contains "Yes".

Is there a way to specifiy a conditional logic statement in the control
source that would only include the line item if the tax field is "Yes"?

The "Immediate If" function IIF will do the trick here:

=IIF([Taxable], [PRItems Subform].Form!ItemSubTotal * 0.0775, 0)

Note that this will not *store* the tax amount anywhere, just display
it - but that is the correct behavior.

One suggestion: tax rates change from time to time. You might want to
consider a) having a tax-rate table to give the current tax rate, and
looking it up in the table rather than hardcoding it in your
expression; and b) actually storing the tax charged in your detail
records to record the taxes as of the time of the sale.

John W. Vinson[MVP]

 

[Tec92407]

I think Iv'e got it.

I added a new calc field to the footer of the subform and named it
"ItemTotalTax".
In the control source, I specified:
=Sum(IIf([ItemTax]="Yes",([ItemQuantity]*[ItemUnitCost])*0.0775,0))

Then, on the main form, in the control source of the total tax field, I
specified:
=[PRItems Subform].Form!ItemTotalTax

This works like I wanted, except that it doesn't compute until the cursor
exits the line item.

More info...

ItemSubTotal is an invisible calculated field in the form footer of the
Items subform. Its purpose is to be able to reference the field values in
each item record to compute the total extended cost of all items. Its control
source contains:

=Sum([ItemQuantity]*[ItemUnitCost])

Problems....

Here's the expression I'm using:

=[PRItems Subform].Form!ItemSubTotal*IIf([PRItems
Subform].Form!ItemTax="Yes",0.0775,0)

But the results are odd.

1. I have the following two entries in the PRItems Subform
Item Qty Cost ExtCost Tax
1. 1 1.00 1.00 No
2. 2 1.00 2.00 Yes
0 0 0.00 0.00 (empty) <-new record line

2. If I select tax=Yes for any or all items, the computed total tax is
always 0.23

3. The value in the computed tax field always reflects the line Item my
cursor is
placed in. If my cursor is in a line item where the tax field=yes, the total
tax is 0.23. If my cursor is in a line item where the tax field=no, or
tax=(blank), the total tax is 0.00.

Probably something obvious, but, what am I missing?

On Mon, 2 Jan 2006 09:52:01 -0800, "Tec92407"

I have a main form with a subform that contains line items. Each line item
has a "Tax" field that contains Yes or No to indicate if tax should be
applied to the item cost. In the main form I have a calculated Tax field that
contains

=[PRItems Subform].Form!ItemSubTotal*0.0775

in its control source property. This works and computes the total tax for
all items. But I need it to include the item in its computation only if the
line item's tax field contains "Yes".

Is there a way to specifiy a conditional logic statement in the control
source that would only include the line item if the tax field is "Yes"?

The "Immediate If" function IIF will do the trick here:

=IIF([Taxable], [PRItems Subform].Form!ItemSubTotal * 0.0775, 0)

Note that this will not *store* the tax amount anywhere, just display
it - but that is the correct behavior.

One suggestion: tax rates change from time to time. You might want to
consider a) having a tax-rate table to give the current tax rate, and
looking it up in the table rather than hardcoding it in your
expression; and b) actually storing the tax charged in your detail
records to record the taxes as of the time of the sale.

John W. Vinson[MVP]