RE: [MS_AccessPros] Grouping "Nots" in query criteria

Thanks brother! Duane To: MS_Access_Professionals@yahoogroups.com
From: graham@mandeno.com
Date: Sat, 28 Jan 2012 18:49:55 +1300
Subject: RE: [MS_AccessPros] Grouping "Nots" in query criteria

Hi Robin

I have the same dominant gene as Duane ;-)

I suggest you create a table with a numeric field named "ExcludeID" or

similar. Populate the table with your exclude numbers, one per record.

Then use this in your WHERE clause:

IDField not in (Select ExcludeID from ExcludeTable)

Then, when your exclude list changes, you only need to change the data, not

the code.

Best wishes,

Graham

> From: MS_Access_Professionals@yahoogroups.com

[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple

> Sent: Saturday, 28 January 2012 16:39

> To: MS_Access_Professionals@yahoogroups.com

> Subject: RE: [MS_AccessPros] Grouping "Nots" in query criteria

>

>

>

> Thanks John,

>

> I had seen that technique and forgot! 'Old Timers' disease.

>

> Regards,

>

> Robin

>

> At 28/01/2012 11:31 AM, you wrote:

> >Or since the numbers appear to be consecutive, you could use:

> >

> >Not BETWEEN 443 And 456

> >

> >John Viescas, author

> >Microsoft Office Access 2010 Inside Out

> >Microsoft Office Access 2007 Inside Out

> >Building Microsoft Access Applications

> >Microsoft Office Access 2003 Inside Out

> >SQL Queries for Mere Mortals

> >http://www.viescas.com/

> >(Lahaina, HI)

> >

> >

> >-----Original Message-----

> >From: MS_Access_Professionals@yahoogroups.com

> >[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane

Hookom

> >Sent: Friday, January 27, 2012 2:21 PM

> >To: Access Professionals Yahoo Group

> >Subject: RE: [MS_AccessPros] Grouping "Nots" in query criteria

> >

> >

> >Robin,You can use: Not In (443, 444, 445, 446, 447, 448, 449, 450, 451,

452,

> >453, 454, 455, 456)

> >However, my anti-hard-coding dominant gene is screaming that these

> >values should

> >be differentiated in a table some place, not hard-coded in an

> >expression. Duane

> >HookomMS Access MVP To: MS_Access_Professionals@yahoogroups.com

> >

> >

> >

> >From: robinski@mymail.net.au

> >Date: Sat, 28 Jan 2012 11:11:59 +1100

> >Subject: [MS_AccessPros] Grouping "Nots" in query criteria

> >

> > I am using Access 2007 with a Windows 7 system.

> >

> >I need to exclude a group of ID numbers from a query. I attempted

> >

> >various groupings and failed. This works:

> >

> ><>443 And <>444 And <>445 And <>446 And <>447 And <>448 And <>449 And

> ><>450 And <>451 And <>452 And <>453 And <>454 And <>455 And <<>456

> >

> >but there must be an elegant way to do that.

> >

> >Many thanks,

> >

> >Robin Chapple

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/