Alternative to DLookup?

[Jim]

I have a tax table with 3 columns: GTET, LTET & Witholding. I need a
function that will look at the GrossPay value on my Payroll Form and then
compare that with the values in the table and return the correct witholding
amount. I know you can do it with DLookUp, but I don't want to hard code all
the values from the table. Is there an easier way to accomplish this? I'm
using Access 2003.
Thanks
Jim

Sample data:

GrossPay Amount 300
Witholding should = 12

GrossPay 315
Witholding should = 13, etc

GTET LTET Witholding
300 310 12
310 320 13
320 330 14

 

[Douglas J. Steele]

Why would you not want to put the values in a table? That's certainly the
usual way of doing it (and it certainly doesn't constitute "hard coding",
since you can change the values in the table any time you need to)

 

[Jim]

I "do" want to put the values in a table. I don't know how to do the
comparison without hard coding them.

 

[John W. Vinson]

I have a tax table with 3 columns: GTET, LTET & Witholding. I need a
function that will look at the GrossPay value on my Payroll Form and then
compare that with the values in the table and return the correct witholding
amount. I know you can do it with DLookUp, but I don't want to hard code all
the values from the table. Is there an easier way to accomplish this? I'm
using Access 2003.
Thanks
Jim

Sample data:

GrossPay Amount 300
Witholding should = 12

GrossPay 315
Witholding should = 13, etc

GTET LTET Witholding
300 310 12
310 320 13
320 330 14

You can use a "Non Equi Join" query to do this. Create a query joining
GrossPay to GTET; open the query in SQL view and change

ON yourtable.GrossPay = taxtable.GTET

to

ON yourtable.GrossPay >= taxtable.GTET AND yourtable.GrossPay < taxtable.LTET

John W. Vinson [MVP]

 

[Jim]

Hello,
When I create the query you suggest, it says Join Expression not supported.
Jim

 

[John W. Vinson]

Hello,
When I create the query you suggest, it says Join Expression not supported.
Jim

What are the datatypes of the fields involved? Could you post the actual SQL?

John W. Vinson [MVP]

 

[Powderfinger]

I have a tax table with 3 columns: GTET, LTET & Witholding. I need a
function that will look at the GrossPay value on my Payroll Form and then
compare that with the values in the table and return the correct witholding
amount. I know you can do it with DLookUp, but I don't want to hard code all
the values from the table. Is there an easier way to accomplish this? I'm
using Access 2003.
Thanks
Jim

Sample data:

GrossPay Amount 300
Witholding should = 12

GrossPay 315
Witholding should = 13, etc

GTET LTET Witholding
300 310 12
310 320 13
320 330 14

Why don't you want to use this? What do you not want to "hard code"?

Dim strCriteria As String
strCriteria = Me.GrossPay & " >=[GTET] AND " & Me.GrossPay & " < [LTET] "
Me.Witholding = DLookup("[Witholding]", "tblTaxTable", strCriteria)

 

[Jim]

It's not that I didn't want to use DLookup, I didn't know the syntax for
referencing a table in code. I had only used it in queries. The only other
way I knew how to do a look up in code was by using Select Case, which is
what I didn't want to do because I thought I would have to hard code all the
values instead of referencing a table.

The code you suggested below worked great. Thanks!

Jim

I have a tax table with 3 columns: GTET, LTET & Witholding. I need a
function that will look at the GrossPay value on my Payroll Form and
then
compare that with the values in the table and return the correct witholding
amount. I know you can do it with DLookUp, but I don't want to hard code all
the values from the table. Is there an easier way to accomplish this? I'm
using Access 2003.
Thanks
Jim

Sample data:

GrossPay Amount 300
Witholding should = 12

GrossPay 315
Witholding should = 13, etc

GTET LTET Witholding
300 310 12
310 320 13
320 330 14

Why don't you want to use this? What do you not want to "hard code"?

Dim strCriteria As String
strCriteria = Me.GrossPay & " >=[GTET] AND " & Me.GrossPay & " < [LTET] "
Me.Witholding = DLookup("[Witholding]", "tblTaxTable", strCriteria)

 

[Jim]

One other issue that popped up..

The function below works great, but the numbers being returned are being
rounded. Is there a simple way to stop this?

The field type for GTET & LTET are both set to number & single. The
witholding field is set to Currency with decimal places at 2.
Thanks

Jim

It's not that I didn't want to use DLookup, I didn't know the syntax for
referencing a table in code. I had only used it in queries. The only
other way I knew how to do a look up in code was by using Select Case,
which is what I didn't want to do because I thought I would have to hard
code all the values instead of referencing a table.

The code you suggested below worked great. Thanks!

Jim

I have a tax table with 3 columns: GTET, LTET & Witholding. I need a
function that will look at the GrossPay value on my Payroll Form and
then
compare that with the values in the table and return the correct witholding
amount. I know you can do it with DLookUp, but I don't want to hard code all
the values from the table. Is there an easier way to accomplish this?
I'm
using Access 2003.
Thanks
Jim

Sample data:

GrossPay Amount 300
Witholding should = 12

GrossPay 315
Witholding should = 13, etc

GTET LTET Witholding
300 310 12
310 320 13
320 330 14

Why don't you want to use this? What do you not want to "hard code"?

Dim strCriteria As String
strCriteria = Me.GrossPay & " >=[GTET] AND " & Me.GrossPay & " < [LTET] "
Me.Witholding = DLookup("[Witholding]", "tblTaxTable", strCriteria)