Dlookup Approximate Value

[C3R8ERU5]

Hi All

I'm a bit of a newbie when it comes to using dlookup.

I have a query that needs to lookup tax values from a table to calculate wage deductions. This is where the problem arises as dlookup only returns exact matches and approximate values are required.

Any help on this matter will be greatly appreciated.

 

[dkinley]

You mean you want to do something like in the attached?

-dK

 

[C3R8ERU5]

You mean you want to do something like in the attached?

-dK

Thanx ever so much, it seems to have solved most of my problems.

 

[dkinley]

Most welcome!

-dK

 

[hyp527]

You mean you want to do something like in the attached?

-dK

What if there's no begin date or end date?

For example: i have only 1 table

tblMainTable
-mainID
-Date
-Wages

how do i used this data to query the prev wage and prev date of the next consecutive wage and date? (I want to query the prev.date and prev.wage column to see the pattern in change)

example: tblMainTable

mainID date wages prev.date? prev.wages?
123 1/1/2009 $12 --- ----
123 1/6/2009 $2.0 1/1/2009 $12
123 1/7/2009 $12.05 1/6/2009 $2.0
123 1/12/2009 $1.05 1/7/2009 $12.05

Thanks.

 

[dkinley]

Hmmm. Not sure about that, perhaps nested queries could get that. I am not that outstanding at working stuff like that so I look at other areas to facilitate it like a a global variable stored in a module that would retain the previous records data and present the data as an extension to the next record. Or perhaps some sorted DAO recordset so you could loop through all of the records and retain the last records information.

-dK

EDIT: Since this is a new line of questioning, I would recommend that this question is asked in a new thread.

 

[hyp527]

Hmmm. Not sure about that, perhaps nested queries could get that. I am not that outstanding at working stuff like that so I look at other areas to facilitate it like a a global variable stored in a module that would retain the previous records data and present the data as an extension to the next record. Or perhaps some sorted DAO recordset so you could loop through all of the records and retain the last records information.

-dK

EDIT: Since this is a new line of questioning, I would recommend that this question is asked in a new thread.

Good advice. Thanks!