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