Dcount to Rank but ignore a zero value? (1 Viewer)

[g-richardson]

I'm trying to build a rank based on a premium value where the smallest premium is ranked 1 and so on. All is working fine except when the premium value =0.

example a. (this is good)

Premium Rank
765 2
800 3
535 1

example b. (not accemptable)

Premium Rank
765 3
800 4
535 2
0 1


example c. (desired)

Premium Rank
765 2
800 3
535 1
0


Rank: DCount("*","TblPremium","Premium<" & [Premium])+1

Gives me what I need when all rows of RecordSet have value >0. I need to find a way to ignore the 0 value and rank the rest beginning with 1.

I have been able to make the zero act like null, but then the ranking starts with 2.

I've looked throughout the forum and haven't been able to find this issue... Any help is much appreciated.

George

 

[Beetle]

Give this a try;

Rank: IIf([Premium]=0,"",DCount("*","tblPremium","Premium <> 0 And Premium<" & [Premium])+1)

 

[g-richardson]

Beetle...Perfect.

Thank you so much, this is exactly what I needed. Thank you for the quick response.