[AccessD] Sort Blanks to the bottom

Stuart McLachlan stuart at lexacorp.com.pg
Sun Nov 2 16:56:37 CST 2008


I would have expected NZ() to be  faster, since you are evalauting a single 
function whereas 
IIF(IsNull()) requires you to evaluate two functions  

But it turns out to be a bit more complex, I've just tried running some big for 
next loops to 
test this.

If Class is a long, there's very little between the two methods - the 
IIF(Isnull()) loop is about 
5% faster than NZ()

If Class is a string, NZ() is definitely more efficient - it's loop is between 20 
and 40% faster 
than IIF(IsNull()) - the higher the proportion of Nulls, the bigger the 
difference!




On 2 Nov 2008 at 13:36, Rocky Smolin at Beach Access  wrote:

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