Dlookup to find the a number =>

[stevefishwickuk]

Hello,

I have had a search through the forums but cannot find anything that is relevant to my issue. Hope someone can help.

I have a table with scores and then grades

tbl_scoregrade

Score,Grade
-999,10
133,9
239,8
342,7
449,6
570,5
709,4
785,3
862,2
917,1

I want to reference a field called "score_result" and generate a grade from my grade table and place it in a field called "grade".

In excel I use the following formula, but it does not work in access.

=IF(ISBLANK(C51),0,VLOOKUP(C51,Scorecard!H7:I13,2,1))

Is there a way of doing this in Access?

 

[Peter Reid]

You could use DMIN; score_grade: IIf(IsNumeric([score_result]),DMIN("Grade","tbl_scoregrade","[Score]<=" & [score_result]),0)


Or DLAST if you create the query 'qry_scoregrade' as; SELECT * FROM tbl_scoregrade ORDER BY tbl_scoregrade.Score;

The formula would then be; score_grade: IIf(IsNumeric([score_result]),DLAST("Grade","qry_scoregrade","[Score]<=" & [score_result]),0)


You could also use VBA and write your own function