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