Query Criteria issue
in [Queries]
|
Prev: How do I bring a filed from multiple records and join them togethe
Next: parent child link query
From: Dave on 14 Apr 2010 12:29 Access 2003: Trying to create a demographics report that will allow the following. Gender example On the form "frmStudentDemographics" is a drop down field named "cboGender" When the user makes a selection "Male" a report is generated for only Males. But in some cases the user will want to run a report for both (all) genders. So I added a check box next to the drop down named "ckAllGender" My idea is that if that box is checked then the report will show information on Both (all) genders. So in the query that generates the report, in the criteria for the Gender field I tried this: IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"", [forms]![frmStudentDemographics]![cboGender]) Note - I also tried: = 1, = yes My logic was that if there was nothing in the criteria ("") it would return all genders. Didn't work. Any suggestions on how to rewrite my criteria OR A different approach to this problem I am just using gender as an example as there will be several Demographic fields with drop downs and I want the user to be able to report on ONE or ALL for any of the fields. Hope I am making sense. Thanks Dave __________ Information from ESET NOD32 Antivirus, version of virus signature database 5029 (20100414) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
From: KARL DEWEY on 14 Apr 2010 13:39 Try this -- Like IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"*", [forms]![frmStudentDemographics]![cboGender]) -- Build a little, test a little. "Dave" wrote: > Access 2003: > > Trying to create a demographics report that will allow the following. > > Gender example > On the form "frmStudentDemographics" is a drop down field named "cboGender" > When the user makes a selection "Male" a report is generated for only Males. > But in some cases the user will want to run a report for both (all) genders. > So I added a check box next to the drop down named "ckAllGender" > My idea is that if that box is checked then the report will show information > on Both (all) genders. > > So in the query that generates the report, in the criteria for the Gender > field I tried this: > > IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"", > [forms]![frmStudentDemographics]![cboGender]) > > Note - I also tried: = 1, = yes > > > My logic was that if there was nothing in the criteria ("") it would return > all genders. > > Didn't work. > > Any suggestions on how to rewrite my criteria > OR > A different approach to this problem > > I am just using gender as an example as there will be several Demographic > fields with drop downs and I want the user to be able to report on ONE or > ALL for any of the fields. > Hope I am making sense. > > Thanks > > Dave > > > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 5029 (20100414) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > >
From: fredg on 14 Apr 2010 13:45 On Wed, 14 Apr 2010 09:29:25 -0700, Dave wrote: > Access 2003: > > Trying to create a demographics report that will allow the following. > > Gender example > On the form "frmStudentDemographics" is a drop down field named "cboGender" > When the user makes a selection "Male" a report is generated for only Males. > But in some cases the user will want to run a report for both (all) genders. > So I added a check box next to the drop down named "ckAllGender" > My idea is that if that box is checked then the report will show information > on Both (all) genders. > > So in the query that generates the report, in the criteria for the Gender > field I tried this: > > IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"", > [forms]![frmStudentDemographics]![cboGender]) > > Note - I also tried: = 1, = yes > > My logic was that if there was nothing in the criteria ("") it would return > all genders. > > Didn't work. > > Any suggestions on how to rewrite my criteria > OR > A different approach to this problem > > I am just using gender as an example as there will be several Demographic > fields with drop downs and I want the user to be able to report on ONE or > ALL for any of the fields. > Hope I am making sense. > > Thanks > > Dave > > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 5029 (20100414) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com As there are only 2 genders, male or female, I would suggest you simply add the word "All" to the combo box list. Set the Combo RowSourceType property to Value List. Set it's RowSource to "All";"Female";"Male" (Change the value order to whatever sort order you wish). Then change the query Where clause to: Like IIf([forms]![frmStudentDemographics]![cboGender] = "All","*", [forms]![frmStudentDemographics]![cboGender]) The user need either select "All" in the combo box or the gender wanted. If you are were using a combo rowsource derived from a query or table, then use a Union query to add the word "All" to the list. Change the table and field names to your actual table and field names. Note that the symbols <> surround the word "All". That is to have the list sort with the word <All> at the top of the list before any other "A" listing. First add "All" to the drop down list. NOTE: The below code will need to be adapted to whatever your specific needs are. The below field and table names are generic. If there are, for example, 2 fields to be included in the combo rowsource, as Combo box Rowsource: Select tblOrderStatus.OrderStatusID,OrderStatus From tblOrderStatus Union Select Null, "<ALL>" from tblOrderStatus Order by OrderStatus; The above shows the OrderStatusID as well as the OrderStatus fields, leaving a blank in the OrderStatusID field where <All> is shown. <All> 15 OptionA 3 OptionB 20 etc... Or�K Do you wish to include just one column in the RowSource? Select tblOrderStatus.OrderStatus From tblOrderStatus Union Select "<ALL>" from tblOrderStatus Order by OrderStatus; <All> OptionA OptionB etc... Then if you are using the OrderStatus to filter data on, change the query criteria to: Like IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName]) The form must be open when the query/report is run. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
From: Dave on 15 Apr 2010 09:57 Thanks to both of you but neither solution worked. Meaning they returned NO data. Any ideas? Thanks Dave "Dave" <dcooper(a)iacnc.edu> wrote in message news:1AE66717-80B1-4A9B-88BE-452F68764242(a)microsoft.com... > Access 2003: > > Trying to create a demographics report that will allow the following. > > Gender example > On the form "frmStudentDemographics" is a drop down field named > "cboGender" > When the user makes a selection "Male" a report is generated for only > Males. > But in some cases the user will want to run a report for both (all) > genders. > So I added a check box next to the drop down named "ckAllGender" > My idea is that if that box is checked then the report will show > information on Both (all) genders. > > So in the query that generates the report, in the criteria for the Gender > field I tried this: > > IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"", > [forms]![frmStudentDemographics]![cboGender]) > > Note - I also tried: = 1, = yes > > > My logic was that if there was nothing in the criteria ("") it would > return all genders. > > Didn't work. > > Any suggestions on how to rewrite my criteria > OR > A different approach to this problem > > I am just using gender as an example as there will be several Demographic > fields with drop downs and I want the user to be able to report on ONE or > ALL for any of the fields. > Hope I am making sense. > > Thanks > > Dave > > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 5029 (20100414) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 5030 (20100415) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 5030 (20100415) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
From: KARL DEWEY on 15 Apr 2010 12:38 A couple of questions. What is the DataType of your Gender field? Post sample of data directly from the table. What is the source of data of your cboGender? -- Build a little, test a little. "Dave" wrote: > Thanks to both of you but neither solution worked. > > Meaning they returned NO data. > > Any ideas? > > Thanks > > Dave > > "Dave" <dcooper(a)iacnc.edu> wrote in message > news:1AE66717-80B1-4A9B-88BE-452F68764242(a)microsoft.com... > > Access 2003: > > > > Trying to create a demographics report that will allow the following. > > > > Gender example > > On the form "frmStudentDemographics" is a drop down field named > > "cboGender" > > When the user makes a selection "Male" a report is generated for only > > Males. > > But in some cases the user will want to run a report for both (all) > > genders. > > So I added a check box next to the drop down named "ckAllGender" > > My idea is that if that box is checked then the report will show > > information on Both (all) genders. > > > > So in the query that generates the report, in the criteria for the Gender > > field I tried this: > > > > IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"", > > [forms]![frmStudentDemographics]![cboGender]) > > > > Note - I also tried: = 1, = yes > > > > > > My logic was that if there was nothing in the criteria ("") it would > > return all genders. > > > > Didn't work. > > > > Any suggestions on how to rewrite my criteria > > OR > > A different approach to this problem > > > > I am just using gender as an example as there will be several Demographic > > fields with drop downs and I want the user to be able to report on ONE or > > ALL for any of the fields. > > Hope I am making sense. > > > > Thanks > > > > Dave > > > > > > > > __________ Information from ESET NOD32 Antivirus, version of virus > > signature database 5029 (20100414) __________ > > > > The message was checked by ESET NOD32 Antivirus. > > > > http://www.eset.com > > > > > > > > > > __________ Information from ESET NOD32 Antivirus, version of virus > > signature database 5030 (20100415) __________ > > > > The message was checked by ESET NOD32 Antivirus. > > > > http://www.eset.com > > > > > > > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 5030 (20100415) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > >
|
Next
|
Last
Pages: 1 2 Prev: How do I bring a filed from multiple records and join them togethe Next: parent child link query |