IIF conditional formattin in a form

[Guest]

I have a field in my form that I want to automatically provide the sales tax,
based on the zipcode. I am using the expression builder. I have 5 different
zipcodes and then "all others". I am successful in doing just one of the
zipcodes and then the all others, but I need to include the other 4 zipcode
options. this example works for me:
=IIf([custzip]=12345,[charged]*0.07,[charged]*0.0055)
How do i add additional custzip, such as:
12456 at 0.07
12567 at 0.07
12678 at 0.65
12789 at 0.65

 

[Guest]

=IIf([custzip]=12345,[charged]*0.07,IIF([CustZip]=22222,[charged]*0.0058,[charged]*0.0055)) and so on and so on and so on.....

Should work ok I have used this where I have at least 14 possibilities....
(Zip Codes)

dwight

 

[Guest]

Don't do this. It would take a very ugly and unreadable group of nested IIf
statements.
You would be better off to create a tax rate table. It will need at least 2
fields:
ZIP_CODE and TAX_RATE.
Put all the zip codes and rate in your table. Add a dummy to be used of the
zip code is not in the table:
ZIP_CODE = "00000"
TAX_RATE = 0.055

Since I don't know how your form is set up or in what order the data is
entered, I am going to suggest you replace the IIf statement you have in the
expression builder. Instead of the expression builder, select Code Builder
and Insert this. You will have to change the names to suit:

Dim sngTaxRate As Single

sngTaxRate = DLookUp("[TAX_RATE]","tblTaxRates","[ZIP_CODE] = '" _
& Me.txtZip & "'")
If IsNull(sngTaxRate) Then
sngTaxRate = DLookUp("[TAX_RATE]","tblTaxRates","[ZIP_CODE] = "00000")
End If
Me.txtTaxAmt = Me.Charged * sngTaxRate

The logic here is that if the zip code is not in the table, the DLookUp will
return Null. So, If you Get a Null back, use the dummy "00000" zip code
which is for all others.
The advantage is that when tax rates go up (we will ignore down for obvious
reasons), it does not take a code change to update the tax rates. Also, no
code changes are required if you need to add a new zip code.

 

[Al Camp]

Karen,
I would suggest a small, seperate ZipCode table, and a TaxRate field to
that table. One field for ZipCode and one for TaxRate. If no tax rate
applies, enter 0 for that zip.
I'd also have a combobox based on that table to allow the user to select
a zip from a predefined list.

Note: you should also be saving the TaxRate value on your main record
table (besides using it in your calculation) in case it changes for that
ZipCode in the future. Not doing that would throw off all the calculations
from past entries if there is a change.

If you use a combo box to select zip code, then the associated tax
rate... in the second column of that combo... could update your [TaxRate]
field for you.
Using the AfterUpdate event of cboZipCode...
[TaxRate] = cboZipCode.Column(1)
(combo columns are numbered 0, 1, 2, 3 etc... so Zip is in 0 and TaxRate is
in 1)

This method allows you to just change... or add... zips with thier
associated taxrates at the table level, and all you code/forms will still
function. "Hard-wiring" your code as you have done will require going back
to your IF statement and re-coding every time changes occur.
hth
Al Camp

 

[Guest]

Al,

Although your solution would work quite well, I would not recommend it. It
still will require code modification to make changes to tax rates or to add
new zip codes.

Karen,
I would suggest a small, seperate ZipCode table, and a TaxRate field to
that table. One field for ZipCode and one for TaxRate. If no tax rate
applies, enter 0 for that zip.
I'd also have a combobox based on that table to allow the user to select
a zip from a predefined list.

Note: you should also be saving the TaxRate value on your main record
table (besides using it in your calculation) in case it changes for that
ZipCode in the future. Not doing that would throw off all the calculations
from past entries if there is a change.

If you use a combo box to select zip code, then the associated tax
rate... in the second column of that combo... could update your [TaxRate]
field for you.
Using the AfterUpdate event of cboZipCode...
[TaxRate] = cboZipCode.Column(1)
(combo columns are numbered 0, 1, 2, 3 etc... so Zip is in 0 and TaxRate is
in 1)

This method allows you to just change... or add... zips with thier
associated taxrates at the table level, and all you code/forms will still
function. "Hard-wiring" your code as you have done will require going back
to your IF statement and re-coding every time changes occur.
hth
Al Camp

I have a field in my form that I want to automatically provide the sales
tax,
based on the zipcode. I am using the expression builder. I have 5
different
zipcodes and then "all others". I am successful in doing just one of the
zipcodes and then the all others, but I need to include the other 4
zipcode
options. this example works for me:
=IIf([custzip]=12345,[charged]*0.07,[charged]*0.0055)
How do i add additional custzip, such as:
12456 at 0.07
12567 at 0.07
12678 at 0.65
12789 at 0.65

 

[Guest]

Thank you so much! Makes sense to me....I will give it a try!

=IIf([custzip]=12345,[charged]*0.07,IIF([CustZip]=22222,[charged]*0.0058,[charged]*0.0055)) and so on and so on and so on.....

Should work ok I have used this where I have at least 14 possibilities....
(Zip Codes)

dwight
--
Hansford D. Cornett

I have a field in my form that I want to automatically provide the sales tax,
based on the zipcode. I am using the expression builder. I have 5 different
zipcodes and then "all others". I am successful in doing just one of the
zipcodes and then the all others, but I need to include the other 4 zipcode
options. this example works for me:
=IIf([custzip]=12345,[charged]*0.07,[charged]*0.0055)
How do i add additional custzip, such as:
12456 at 0.07
12567 at 0.07
12678 at 0.65
12789 at 0.65

 

[Al Camp]

Hi Klaatu,
How have you been getting along?

I have to respectfully disagree... or atleast try to support my response.

It still will require code modification to make changes to tax rates or to
add
new zip codes.

My method uses no code other than updating the tax rate field once a zip
(AfterUpdate) is selected from the combo.

I avoid the Dlookup function code by taking the TaxRate value directly from
the ZipCode combo Column(1)

*I think the key issue in our discussion is... you are using the TaxRate
value to directly calculate txtTaxAmount, whereas I encouraged the poster to
create a TaxRate field in her table, and on the form, and to capture that
value to a field. Tax rates change... (usually up... and often... to our
dismay), so it's critical to capture it at record creation, and just use a
calculated field to display the result. Just as you would with a
[ProductPrice].
= Me.TaxRate * Me.Charged
In your method, you have to code for nulls because your using the Dlookup
directly in the calculation.

In both our solutions, if a totally new zip was entered on the form, the
resulting tax rate on the form would 0. But, since there may truly be a tax
rate for that zip, the user would still have to update the Zip table
anyway... eventually. And in either case, a TaxRate manual entry can be
made on the form until the ZipCode table is corrected.
In other words, neither of our solutions handle new (not in the table) zip
codes. We both yield 0's which may or may not be correct.

Thanks,
Al Camp
http://home.comcast.net/~cccsolutions/index.html (Access Tips still under
construction)

Al,

Although your solution would work quite well, I would not recommend it.
It
still will require code modification to make changes to tax rates or to
add
new zip codes.

Karen,
I would suggest a small, seperate ZipCode table, and a TaxRate field
to
that table. One field for ZipCode and one for TaxRate. If no tax rate
applies, enter 0 for that zip.
I'd also have a combobox based on that table to allow the user to
select
a zip from a predefined list.

Note: you should also be saving the TaxRate value on your main record
table (besides using it in your calculation) in case it changes for that
ZipCode in the future. Not doing that would throw off all the
calculations
from past entries if there is a change.

If you use a combo box to select zip code, then the associated tax
rate... in the second column of that combo... could update your [TaxRate]
field for you.
Using the AfterUpdate event of cboZipCode...
[TaxRate] = cboZipCode.Column(1)
(combo columns are numbered 0, 1, 2, 3 etc... so Zip is in 0 and TaxRate
is
in 1)

This method allows you to just change... or add... zips with thier
associated taxrates at the table level, and all you code/forms will still
function. "Hard-wiring" your code as you have done will require going
back
to your IF statement and re-coding every time changes occur.
hth
Al Camp

I have a field in my form that I want to automatically provide the sales
tax,
based on the zipcode. I am using the expression builder. I have 5
different
zipcodes and then "all others". I am successful in doing just one of
the
zipcodes and then the all others, but I need to include the other 4
zipcode
options. this example works for me:
=IIf([custzip]=12345,[charged]*0.07,[charged]*0.0055)
How do i add additional custzip, such as:
12456 at 0.07
12567 at 0.07
12678 at 0.65
12789 at 0.65