[AccessD] Sort Blanks to the bottom

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


Mmm, unless I misread the problem I'm surprised that works Rocky. Where
Class is not Null all values of Class get sorted with the same value (1)
from the Isnull, so it's in the lap of the gods which sequence thaey come
out in. That's why I had IIf(IsNull(Class),2,1) followed by Class then
Variety. The logic is that all values where Class is not null sort before
those where it is (ie 1 before 2) then within that you sort on Class itself
(which makes no difference where Class is null but does where it isn't) then
finally in all cases you sort on Variety. If you see what I mean.

Andy

-----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 17:22
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Sort Blanks to the bottom


Andy:

Very clever!  And works with only minor mods:

SELECT VarietyTBL.VarietyID, VarietyTBL.Class, VarietyTBL.Variety FROM
VarietyTBL ORDER BY IIf(IsNull(Class),2,1), VarietyTBL.Variety;
 
Thank you.

Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
Sent: Sunday, November 02, 2008 9: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

-- 
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