Perform Lookup in excel based on Access Criteria

[adam.grendell]

Hi all,

Not sure if this is the correct forum, but here goes.

I am creating a payroll db. The project is all but complete. The one thing I would like to do is find a way to perform data retrieval based on criteria. On the actual Payroll form, where the employer will figure up the check amount. I have a subform that pops up, giving the pay rate, marital status, and exemptions. I want Access to be able to automatically enter the correct Federal and State withholdings based on their Marital status and Exemptions. I have the tables from the IRS Publication 15. I just need a way to make Access read the data and enter the amount based on the criteria. I figured I could use a linked Excel table. I'm just not sure on how to make this work. It may be as simple as a macro, or it may be VBA, or even something else. I can't quite wrap my mind around the matter.

Any help would be greatly appreciated!

Thanks,

Adam

 

[adam.grendell]

bump........

 

[Beetle]

There's not really enough info in your post to provide any specific answer, but if this is a linked Excel table then you should be able to use any of the standard methods for data retrieval (query, DLookup, etc.).

 

[adam.grendell]

Thanks for the reply.

Here is the breakdown...

The data is the federal withholdings table. My lookup criteria are the amount of gross pay (ex. between 175-185) and the number of exemptions. I need this data to automatically retrieve the correct amount of withholding based on these two criteria.

A complete, real example would be:

If employee a makes $500 Gross pay, and has 2 exemptions, he should have $38 held out for taxes. I need the $38 to automatically be placed in the "Federal" textbox on my form. I know that it can be done, I'm just not really good with dlookup.

 

[adam.grendell]

Okay. I think I'm getting closer to what I need.
Something like this:

I have a table that has 5 columns: Marital Status, Exemptions, PayMin, PayMax, and Withholding.

I need to run a dlookup on this table based on the number of exemptions and marital status, as well as find the amount between the PayMin and PayMax fields to pull the Withholding field into a text box on my form.

I'm not good at dlookup, but maybe something like this:

Federal = Dlookup("Withholdings", "tblFedMarWeekly", (here's where I get lost) Exemptions = forms!frmEmployeesSub!Exemptions AND forms!frmPayroll!TotalGrs >= PayMin AND <=PayMax

Any help on composing this Dlookup???
I've tried looking up tools for this but nothing quite fit my problem.

Any help is greatly appreciated!

 

[Beetle]

It would look something like the following;

Dlookup("Withholding", "tblFedMarWeekly", "[Marital Status]= " & Forms!frmEmployeesSub![Marital Status] & " AND Exemptions = " & Forms!frmPayroll!Exemptions &
" AND PayMin <= " & Forms!frmPayroll!TotalGrs & " AND PayMax >= " & Forms!frmPayroll!TotalGrs)

Keep in mind that there may be slight changes necessary depending on where you are using this (i.e. in your forms code module or in the Control Source of a text box) and what the data types are of those fields in your forms record source (if any of them are text then some string delimiters will need to be added).

 

[adam.grendell]

After lots (an I mean LOTS) of trial and error to get this to work like I want, I think I need a new approach. I read online about using something called a recordset in VBA. Could I apply this since the data is in a table, and maybe use if/then statements for the rest? Thanks again for all the help!