Changing 5098 to the Last Number in a Rank Plus One?

[TBC]

something like this? Expr1: IIf([3MonthRank]="*5089*",0,[3MonthRank])

Would someone please take a look at my database and help me find a way to change the “5089” to just be the last number in my ranking.

So in 3MonthRank all the “5089’s” should be 692
And in 6MonthRank all the “5089’s” should be 750

I’m hoping there is a easy way to do this.

The attached will show you exactly what I’m asking

I really appreciate you taking the time to help.

Below is the code if that helps.

[sql](Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[3MonthTotalRecord$] < B.[3MonthTotalRecord$]+1) AS 3MonthRank,
Wholesale_Group_1_export_tbl.[3MonthTotalRecord$],
Wholesale_Group_1_export_tbl.[3MonthTotalRecords#],

(Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[6MonthTotalRecords$] < B.[6MonthTotalRecords$]+1) AS 6MonthRank,
Wholesale_Group_1_export_tbl.[6MonthTotalRecords$],
Wholesale_Group_1_export_tbl.[6MonthTotalRecords#],

(Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[9MonthTotalRecords$] < B.[9MonthTotalRecords$]+1) AS 9MonthRank, Wholesale_Group_1_export_tbl.[9MonthTotalRecords$], Wholesale_Group_1_export_tbl.[9MonthTotalRecords#], (Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[12MonthTotalRecord$] < B.[12MonthTotalRecord$]+1) AS 12MonthRank, Wholesale_Group_1_export_tbl.[12MonthTotalRecord$], Wholesale_Group_1_export_tbl.[12MonthTotalRecords#] INTO RankTable_tbl

FROM Wholesale_Group_1_export_tbl
ORDER BY Wholesale_Group_1_export_tbl.[3MonthTotalRecord$] DESC , Wholesale_Group_1_export_tbl.[6MonthTotalRecords$] DESC , Wholesale_Group_1_export_tbl.[9MonthTotalRecords$] DESC;[/sql]

 

[Guus2005]

Use Dcount instead of count which means you have to rewrite your query. The result will be better readable.