Criteria in Querry to include All and Empty Field
in [General]
|
Prev: What is a "Flat File" in Access?
Next: wcv
From: Tom Ventouris on 1 Mar 2010 00:48 Thanks. I learned this one the hard way. "John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:9p4ko5pbss5mlfpr616722af3ke5h2jb2i(a)4ax.com... > On Sat, 27 Feb 2010 22:26:01 -0800, Tom Ventouris > <TomVentouris(a)discussions.microsoft.com> wrote: > >>Thank you. I see, now, the significance of putting the criteria in the SQL >>statement. It's all working. >> >>"KenSheridan via AccessMonster.com" wrote: >> >>> This is why its much easier to write (and save) the query as SQL; you >>> just >>> tack each parenthesised OR operation together in a set of AND >>> operations: >>> > > Just one warning: if you carefully construct a beautiful such > multicriterion > query in SQL... and then open it in Query Design view... Access will turn > it > into a total mess. It may in fact cease to work correctly, and it will > totally > scramble your SQL (adding a calculated field for each criterion, etc.) > So... > create it in SQL and *NEVER* open it in the query design grid! > -- > > John W. Vinson [MVP]
From: joelgeraldine on 17 Mar 2010 09:34
ù*,jlmù "Tom Ventouris" <TomVentouris(a)discussions.microsoft.com> a écrit dans le message de groupe de discussion : CA794904-AC42-4107-B406-5B53356784FB(a)microsoft.com... > Thank you. I see, now, the significance of putting the criteria in the SQL > statement. It's all working. > > "KenSheridan via AccessMonster.com" wrote: > >> This is why its much easier to write (and save) the query as SQL; you >> just >> tack each parenthesised OR operation together in a set of AND operations: >> >> SELECT * >> FROM SomeTable >> WHERE (SomeField = [SomeParameter] >> OR [SomeParameter] IS NULL) >> AND (SomeOtherField = [SomeOtherParameter] >> OR [SomeOtherParameter] IS NULL) >> AND (YetAnotherField = [YetAnotherParameter] >> OR [YetAnotherParameter] IS NULL) >> <and so on>; >> >> Ken Sheridan >> Stafford, England >> >> Tom Ventouris wrote: >> >Thank you. This one seems to work too for one or two fields with >> >criteria, >> >however for some reason, as add more criteria in other fields, the >> >Criteria >> >lines in the query multiply. I cannot tell how many, but enough to cause >> >Access to crash. >> > >> >> Try this as the 'criteria' in query design view, all on one line: >> >> >> >[quoted text clipped - 35 lines] >> >> >>> source data tables? >> >> >>> Thanks in advance. >> >> -- >> Message posted via http://www.accessmonster.com >> >> . >> |