[AccessD] Sort Blanks to the bottom

Rocky Smolin at Beach Access Software rockysmolin at bchacc.com
Sun Nov 2 15:36:06 CST 2008


That would work, too.  You think evaluating Nz or iif is faster? 


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 Stuart McLachlan
Sent: Sunday, November 02, 2008 1:03 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Sort Blanks to the bottom

The VBA NZ() function , which is like the TSQL ISNULL() is simpler in this
case 


SELECT VarietyID, Class, Variety 
FROM VarietyTBL 
ORDER BY NZ(Class,999999999),Variety;


or, if Class if text, use  NZ(Class,"zzzzzzzzzzzzzzzzzzzzzzzzz")

On 2 Nov 2008 at 11:39, Rocky Smolin at Beach Access  wrote:

> Andy:
> 
> As 'a good program is one that works' I usually don't spend too much time
> after that. :)  
> 
> I tear out too much hair trying to figure out why something doesn't work.
> When it works I just kind of walk away quietly.  
> 
> But it's working and a good solution too.  Would make a good tip for one
of
> Susan's articles.
> 
> 
> 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 10:09 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Sort Blanks to the bottom
> 
> 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
> 
> 
> -- 
> 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