Rank Order Manipulation

[g-richardson]

Hi All,

While creating a report I have a list that I have sorted in lowest to highest and then ranked accordingly.

I need to keep the ranking sorted lowest to highest with one exception. I have some records that have a null value (could be zero, but currently null). I need to have this value drop to the bottom of the list. I still need to have them captured and ranked, I just need them at the bottom of the list and ranked at the bottom of the list.

Thoughts?

Thanks,
George

 

[gemma-the-husky]

you probably need another column in your query, to sort the nulls so that

if isnull(testfield,chr(127),testfield)

not sure what chr(127) is - its just you need a char after any alpha-numeric, to sort the NULLS AFTER the real values.

then sort on this column.

 

[g-richardson]

Thanks Jemma...You have done it again. This was the key I was looking for.

I'm cleaning up someone elses work and went to the three queries used in a join sequal and added a new column iff ReportList: IIf(

• Is Null,"Fails GLP Test",
  • ), I then added the replaced "List" with "ReportList" and sorted on that field...did the trick
    
    Thanks again.
    
    George