Rocky Smolin at Beach Access Software
rockysmolin at bchacc.com
Sun Nov 2 13:39:12 CST 2008
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