[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