Lookup function

[sunnig]

I have tried dlookup until I am blue in the face. I have a query that calculates a value "MCBDValue". Based on this value I need to lookup the permit cost in table. For example let's say that MCBDValue = 224,670. I need it to go look at the permit cost table, determine that MCBDValue is between 224,000 and 225,000 and return the permit cost of $738.00. The permit table has a min value and a max value and a given permit cost for the values between those numbers. Help Please!!!

 

[dhookom]

How about some actual table/query and field names? Can you take a minute to give us a few sample records? I think you might need to nest a couple DLookup()s or DMax() etc to first find a value for an exact match.

I would probably just create a simple user-defined function that accepts an MCBDValue and returns the permit cost.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

=DLookUp("[permit cost]","cost table",MCBDValue & " BETWEEN [min value] And [max value]")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886