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