[AccessD] Sort Blanks to the bottom

Andy Lacey andy at minstersystems.co.uk
Sun Nov 2 11:12:09 CST 2008


Hi Rocky

How about just

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


Andy Lacey


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin at
Beach Access Software
Sent: 02 November 2008 16:56
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Sort Blanks to the bottom


Dear List:
 
I have to create a record source for a list box of three fields Field1,
Field2, Field3.  For some records Field2 can be null.  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.  Can't seem to
get this one.  I tried a union query of two queries one selecting null field
2 and one selecting non-null field 2 but the nulls still come out on top:

SELECT VarietyID, Class, Variety FROM VarietyTBL WHERE VarietyTBL.Class Is
Not Null ORDER BY Class, Variety Union Select VarietyID, Class, Variety from
VarietyTBL WHERE VarietyTBL.Class Is Null ORDER BY Class, Variety 
 
Anyone have a solution to this?

MTIA
 
 


 	
	
 

Rocky Smolin

Beach Access Software

858-259-4334

www.e-z-mrp.com <http://www.e-z-mrp.com/> 

www.bchacc.com <http://www.bchacc.com/> 

 

 


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





More information about the AccessD mailing list