[AccessD] Sort Blanks to the bottom

Sorry Stuart but the earlier solutions most certainly do. If you go back to
my very first reply it's

SELECT VarietyID, Class, Variety FROM VarietyTBL WHERE VarietyTBL.Class Is
Not Null ORDER BY iif(IsNull(Class),2,1), Class, Variety 

And I subsequently attempted to explain why it needs the second entry of
just Class in the sort sequence but, as Steve says, Rocky's going with
something else.

Andy

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: 02 November 2008 22:57
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Sort Blanks to the bottom


But it does what Rocky originally asked for, the earlier solutions don't :-)

<quote>
the client wants the non-null field2 records at the top sorted by field two
then 
field3, and the 
records with a null field 2 at the bottom sorted by field3.  
</quote>


On 3 Nov 2008 at 11:27, Steve Schapel wrote:

> Hi Rocky,
> 
> Yes it will sort the Nulls to the bottom.
> 
> But the non-Nulls will be sorted according to the Class value, which 
> is
> not what will happen with:
> SELECT VarietyTBL.VarietyID, VarietyTBL.Class, VarietyTBL.Variety
> FROM VarietyTBL
> ORDER BY IIf(IsNull(Class),2,1), VarietyTBL.Variety
> 
> or with:
> SELECT VarietyTBL.VarietyID, VarietyTBL.Class, VarietyTBL.Variety FROM 
> VarietyTBL ORDER BY IsNull(Class) DESC, VarietyTBL.Variety
> 
> Regards
> Steve
> 
> 
> Rocky Smolin at Beach Access Software wrote:
> > How different?  And how come?  Wouldn't NZ(Class,9999) sort the 
> > nulls to the bottom?
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com