Rank Order Manipulation (1 Viewer)

g-richardson

Registered User
Joined
Mar 4, 2005
Messages
42
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

Super Moderator
Staff member
Joined
Sep 12, 2006
Messages
13,873
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

Registered User
Joined
Mar 4, 2005
Messages
42
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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom