[AccessD] Sort Blanks to the bottom

 Yes Andy has a better idea.

 BTW, I reversed the IsNull parameters
 to sort nulls to the bottom.

 SELECT tt002TaskItems.TaskType,
        tt002TaskItems.TaskShortDesc,
        IIf([tt002TaskItems]![HotelSite],1,2) AS NullBottom
 FROM
        tt002TaskItems
 ORDER BY
        IIf([tt002TaskItems]![HotelSite],1,2),
        tt002TaskItems.TaskType;


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Andy Lacey
Sent: Sunday, November 02, 2008 10:12 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Sort Blanks to the bottom


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


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