Rank Order Manipulation (1 Viewer)

g-richardson

Registered User.
Local time
Today, 15:10
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
Local time
Today, 19:10
Joined
Sep 12, 2006
Messages
15,614
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.
Local time
Today, 15:10
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

Top Bottom