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 27 Feb 2010 01:59 Thanks again. I did not try it in SQL view, apologies, the first instruction was clear. I have now tried it in SQL View and works. I see that the query criteria appear as Expr1 in Design View. I have created additional criteria and they appear as Expr2 through to Expr5. This works exactly as expected. Thank you again for coming back after my second posting. "Allen Browne" <AllenBrowne(a)SeeSig.Invalid> wrote in message news:%230MVjjttKHA.4332(a)TK2MSFTNGP05.phx.gbl... > Did you try it in SQL View? > > (Or did you merely type this into the Criteria row in design view?) > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > > "Tom Ventouris" <tom(a)pmads.co.za> wrote in message > news:7A4CA7D8-FDBE-4E67-91B9-0807FD22A2D0(a)microsoft.com... >> Thank you. >> I have tried changing the query criteria, however: >> 1. When I select a value in myComboBox, the query does not return any >> records. (They are there) >> 2. When I select <<ALL>> in myComboBox, I get " the query is too complex >> to be evaluated...." >> >> >> "Allen Browne" <AllenBrowne(a)SeeSig.Invalid> wrote in message >> news:uKVeicrtKHA.4220(a)TK2MSFTNGP05.phx.gbl... >>> It is possible to massage the WHERE clause of the query so that it >>> evaluates to True for all records if the combo's value is "<<ALL>>". >>> >>> Switch the query to SQL View. >>> Locate the WHERE clause. >>> Set it up like this: >>> WHERE IIf([Forms]![frmMyForm]![MyComboBox]="<<ALL>>", True, >>> [SomeField] = [Forms]![frmMyForm]![MyComboBox]) >>> >>> This gets tiresome and clumsy where you have lots of fields to filter >>> on, so it's probably better to learn how to do it in the filter of the >>> form rather than directly in the query. Here's an example: >>> Search form - Handle many optional criteria >>> at: >>> http://allenbrowne.com/ser-62.html >>> >>> -- >>> Allen Browne - Microsoft MVP. Perth, Western Australia >>> Tips for Access users - http://allenbrowne.com/tips.html >>> Reply to group, rather than allenbrowne at mvps dot org. >>> >>> >>> "Tom Ventouris" <tom(a)pmads.co.za> wrote in message >>> news:DA9C2547-EBAC-432A-9112-EA8A2CD28B25(a)microsoft.com... >>>> I run a query from a form where the criteria are selected from a combo >>>> box. The combo box is based obn a Union Query to include <<ALL>>. >>>> >>>> The crieria I set in myField in the query: >>>> Like >>>> (IIf([Forms]![frmMyForm]![MyComboBox]="<<ALL>>","*",[Forms]![frmMyForm]![MyComboBox])) >>>> How can I get the query to include records where myField is blank in >>>> the source data tables? >>>> Thanks in advance. >>> >>
From: Tom Ventouris on 27 Feb 2010 02:05 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. "KenSheridan via AccessMonster.com" wrote: > Try this as the 'criteria' in query design view, all on one line: > > [Forms]![frmMyForm]![MyComboBox] Or [Forms]![frmMyForm]![MyComboBox] > ="<<ALL>>" > > In SQL view: > > WHERE ([SomeField] = [Forms]![frmMyForm]![MyComboBox] > OR [Forms]![frmMyForm]![MyComboBox]="<<ALL>>") > > If the value in the field equals the selected value in the combo box then the > first part of the OR operation in the expression will evaluate to True for > that row, so the row will be returned; if <<ALL>> is selected in the combo > box then the other part of the OR operation in the expression will evaluate > to True regardless of the value (or absence of a value) in the field, so > every row will be returned. > > Note that if you set this up in query design view, and save the query, when > you open it again in design view Access will have moved things around. The > logic will be the same, however, and it will work just the same. If using > multiple 'optionalized' parameters by tacking a series of OR operations > together with ANDs. the logic is far clearer in SQL, so it makes sense to > save the query in SQL view if later amendments are contemplated. > > Ken Sheridan > Stafford, England > > Tom Ventouris wrote: > >Thank you. > >I have tried changing the query criteria, however: > >1. When I select a value in myComboBox, the query does not return any > >records. (They are there) > >2. When I select <<ALL>> in myComboBox, I get " the query is too complex to > >be evaluated...." > > > >> It is possible to massage the WHERE clause of the query so that it > >> evaluates to True for all records if the combo's value is "<<ALL>>". > >[quoted text clipped - 21 lines] > >>> source data tables? > >>> Thanks in advance. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access/201002/1 > > . >
From: KenSheridan via AccessMonster.com on 27 Feb 2010 08:02 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
From: Tom Ventouris on 28 Feb 2010 01:26 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 > > . >
From: John W. Vinson on 28 Feb 2010 01:56
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] |