IIF statement with multiple conditions

[Cindy - Atlanta,GA]

Is there a way to accomplish this calculation in Access? I need to reference
the state and the date to determine the percentage to apply to the
calculation. For example, if the state is GA and the date is between
20030501 and 20040430 apply 4%, if the state is GA and the date is between
20050501 and 20060430, apply 3.5% etc.

These are the formulas and states (I6 is the date being referenced)

AL
=IIF(I6>=20070501,0.006,IIF(I6>=20060501<=20070430,0.005,IIF(I6>=20040501<=20060430,0.007,IIF(I6>=20020501<=20040430,0.009,0))))

DC =IIF(I6>=20070501,0.111,0)

GA
=IIF(I6>=20060501<=20070430,0.088,IF(I6>=20040501<20060430,0.116,IIF(I6>=20020501<=20040430,0.081,0)))

ID =IIF(I6>=20040501<=20060430,0.033,IIF(I6>=20060501,0.031,0))

KS
=IIF(I6>=20070501,0.034,IIF(I6>=20060501<=20070430,0.035,IIF(I6>=20040501<=20060430,0.032,IIF(I6>=20020501<=20040430,0.04,0.094))))

MN =IIF(I6>=20010501<=20020430,0.162,IIF I6>=20020501<=20030430, 0.107,0))

SC
=IIFF(I6>=20070501,0.245,IIF(I6>=20060501<=20070430,0.194,IIF(I6>=20040501<=20060430,0.23,IIF(I6>=20020501<=20040430,0.158,0.145))))

WI =IIF(I6>=20070501,0.018,0)

 

[Arvin Meyer [MVP]]

I'm going to assume that your dates are not real date datatypes since the
way you are formatting them is useless to SQL. I would probably build a
function and call it in a query or as the controlsource of a textbox in a
form or report.

Either use a recordset to get the State data from a table, or just use a
Select Case statement. Personally, I'd prefer the recordset because it is
more elegant, more scalable (as you add states), and much easier to
maintain.

 

[Cindy - Atlanta,GA]

Hi Arvin,

Thanks for the response. I'm a novice with SQL and Access, so I don't
really know where to begin with either the Select Case or the recordset
functions. Can you give me an example? It's true that the dates are not set
as "date" data types - they're long integers only because that's the format
used in the data file I imported to Access.

 

[Arvin Meyer [MVP]]

So first, you must decide on whether or not a city or county sales tax is
applicable. For instance here in Florida each county has a different State
Sales Tax and State Gasoline Tax. Assuming that's not a consideration, I'd
build a table for the recordset to call upon with the following fields:

tblStateTax
TaxID - Primary Key (Autonumber)
State - Text
TaxStart - Long
TaxEnd - Long
TaxRate - Double

Then I'd build a function something like: (Keep in mind that this is
untested "aircode" typed directly into this post without even a spell check)
Also make sure that you have set a reference to DAO in the Tools menu of
your code window.

Public Function GetTax(strState As String, lngDate As Long) As Double
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select * From tblStateTax Where State = '" & strState & "'" And _
TaxStart >lngDate And TaxEnd <lngDate"
Set rst = db.OpenRecordset (strSQL)

GetTax = rst!TaxRate

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Error_Handler:
On Error Resume Exit_Here
End Function

Then you can use the function in a query like:

Select GetTax("GA", Forms!SomeForm!SomeDate) As CalcRate From MyTable.

If you are having problems, use the email address from the DataStrat website
below. I'll be at a tech meeting tonight, but I can help tomorrow) Other
MVPs can help as well.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Cindy - Atlanta,GA]

Thanks so much for your help Arvin!

So first, you must decide on whether or not a city or county sales tax is
applicable. For instance here in Florida each county has a different State
Sales Tax and State Gasoline Tax. Assuming that's not a consideration, I'd
build a table for the recordset to call upon with the following fields:

tblStateTax
TaxID - Primary Key (Autonumber)
State - Text
TaxStart - Long
TaxEnd - Long
TaxRate - Double

Then I'd build a function something like: (Keep in mind that this is
untested "aircode" typed directly into this post without even a spell check)
Also make sure that you have set a reference to DAO in the Tools menu of
your code window.

Public Function GetTax(strState As String, lngDate As Long) As Double
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select * From tblStateTax Where State = '" & strState & "'" And _
TaxStart >lngDate And TaxEnd <lngDate"
Set rst = db.OpenRecordset (strSQL)

GetTax = rst!TaxRate

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Error_Handler:
On Error Resume Exit_Here
End Function

Then you can use the function in a query like:

Select GetTax("GA", Forms!SomeForm!SomeDate) As CalcRate From MyTable.

If you are having problems, use the email address from the DataStrat website
below. I'll be at a tech meeting tonight, but I can help tomorrow) Other
MVPs can help as well.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com