DLookUp function and a range

[jex57]

Hi
I am trying to use a Dlookup with a range:

=DLookUp("[Symbol]","[Grade]","[Grade (%)] >= [Grade]![Grade_min] AND [Grade (%)] <= [Grade]![Grade_max]")


No matter what my grade (%) I always get the lowest value - it does not seem to look up the range.

Any ideas?

Thank you

 

[Micron]

with a range:

Is this an Excel question, because you posted in the Access part of the forum? If not, then what is Grade! and Grade_max - form and control references?
If so, try this format (assuming the criteria being passed are numbers)
"[Grade (%)] >= " & Forms![Grade]![Grade_min] & " AND [Grade (%)] <= " & Forms![Grade]![Grade_max])

 

[Joe4]

Is this an Excel question, because you posted in the Access part of the forum?

I am pretty certain it is an Access question (which means it is posted in the correct place).
DLOOKUP is an Access function (Excel does not have a DLOOKUP function).

 

[Micron]

Was not sure, because Access doesn't have a "range" either, but I am not as familiar with Excel functions as I am with Access.
Thanks.
P.S. - do you think my answer is on the right track? I ask because I think you are pretty good with Access...

 

[Joe4]

P.S. - do you think my answer is on the right track? I ask because I think you are pretty good with Access...

Yes, at first casual glance your formula looks pretty good to me.

I think maybe the confusion is in the word "range". I don't think that they are talking about a "range of cells", like we use in Excel, but rather a "numerical range" (where they are looking for records between a low and high value they are specifying).

I also think there is some ambiguity with the question. I cannot tell if they are expecting more than one record to be returned, which would be problematic, because DLOOKUP only returns a single value.

jex57,
It may be helpful if you could just explain in English exactly what you are looking for, and maybe provide a small sample of data and your expected results.