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 26 Feb 2010 00:39 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: Allen Browne on 26 Feb 2010 02:41 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 26 Feb 2010 05:27 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: KenSheridan via AccessMonster.com on 26 Feb 2010 06:09 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: Allen Browne on 26 Feb 2010 06:43
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. >> > |