Report Parameter not displaying correct results
in [Reports]
|
Prev: Send different reports to different printers
Next: marvendas@gmail.com Kit completo deSolenóides ( solenoid ) + chicote Para Câmbio automatico hidramatico Audi A3 Vw Golf gti turbo 52023
From: gmazza via AccessMonster.com on 18 Feb 2010 18:04 Hey there, I have a parameter form that you can choose a Description from or ALL. Its based on this query: SELECT Description, 1 AS SortColumn FROM Study UNION SELECT "All", 0 FROM Study ORDER BY SortColumn, [Description]; When I choose ALL I get no data on my report when there is data. MY RecordSource for the report is: SELECT Study.Description, Study.Product, Study.Dosage FROM Study WHERE ((Study.Description)=forms!SelectTrial!txtDescription) OR((forms!SelectTrial!txtDescription)="ALL") If I choose ALL shouldn't all my Descriptions show up on the report or am I doing something wrong? Please help, thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201002/1
From: KARL DEWEY on 18 Feb 2010 19:06 Your "All" is text and not a function so it won't work that way. I do not know why you need the first query but change the second to this -- SELECT Study.Description, Study.Product, Study.Dosage FROM Study WHERE ((Study.Description)=forms!SelectTrial!txtDescription) OR (Study.Description like IIF(forms!SelectTrial!txtDescription)="ALL", "*",)) -- Build a little, test a little. "gmazza via AccessMonster.com" wrote: > Hey there, > I have a parameter form that you can choose a Description from or ALL. Its > based on this query: > SELECT Description, 1 AS SortColumn FROM Study UNION SELECT "All", 0 FROM > Study > ORDER BY SortColumn, [Description]; > > When I choose ALL I get no data on my report when there is data. > > MY RecordSource for the report is: > SELECT Study.Description, Study.Product, Study.Dosage > FROM Study > WHERE ((Study.Description)=forms!SelectTrial!txtDescription) > OR((forms!SelectTrial!txtDescription)="ALL") > > If I choose ALL shouldn't all my Descriptions show up on the report or am I > doing something wrong? > > Please help, thanks! > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201002/1 > > . >
From: gmazza via AccessMonster.com on 19 Feb 2010 11:03 I'm getting syntax errors with your query. I took out an extra ) in the last line but it still errored. I deleted the last comma, and it still said the same thing. Says syntax error (comma) in query expression. As for my first query, I added it because I want the combo box to display the records, as well as ALL, so the user can choose the a description or choose ALL to return all descriptions. Thanks! KARL DEWEY wrote: >Your "All" is text and not a function so it won't work that way. >I do not know why you need the first query but change the second to this -- >SELECT Study.Description, Study.Product, Study.Dosage >FROM Study >WHERE ((Study.Description)=forms!SelectTrial!txtDescription) >OR (Study.Description like IIF(forms!SelectTrial!txtDescription)="ALL", "*",)) > >> Hey there, >> I have a parameter form that you can choose a Description from or ALL. Its >[quoted text clipped - 15 lines] >> >> Please help, thanks! -- Message posted via http://www.accessmonster.com
From: KARL DEWEY on 19 Feb 2010 12:14 Try now -- SELECT Study.Description, Study.Product, Study.Dosage FROM Study WHERE (Study.Description = [forms]![SelectTrial]![txtDescription]) OR (Study.Description like IIF([forms]![SelectTrial]![txtDescription] = "ALL", "*",)); If you still get syntax error remove part of WHERE statement like this -- SELECT Study.Description, Study.Product, Study.Dosage FROM Study WHERE (Study.Description = [forms]![SelectTrial]![txtDescription]); or this -- SELECT Study.Description, Study.Product, Study.Dosage FROM Study WHERE (Study.Description like IIF([forms]![SelectTrial]![txtDescription] = "ALL", "*",)) -- Build a little, test a little. "gmazza via AccessMonster.com" wrote: > I'm getting syntax errors with your query. > I took out an extra ) in the last line but it still errored. I deleted the > last comma, and it still said the same thing. Says syntax error (comma) in > query expression. > > As for my first query, I added it because I want the combo box to display the > records, as well as ALL, so the user can choose the a description or choose > ALL to return all descriptions. > Thanks! > > KARL DEWEY wrote: > >Your "All" is text and not a function so it won't work that way. > >I do not know why you need the first query but change the second to this -- > >SELECT Study.Description, Study.Product, Study.Dosage > >FROM Study > >WHERE ((Study.Description)=forms!SelectTrial!txtDescription) > >OR (Study.Description like IIF(forms!SelectTrial!txtDescription)="ALL", "*",)) > > > >> Hey there, > >> I have a parameter form that you can choose a Description from or ALL. Its > >[quoted text clipped - 15 lines] > >> > >> Please help, thanks! > > -- > Message posted via http://www.accessmonster.com > > . >
From: gmazza via AccessMonster.com on 19 Feb 2010 16:53
Thanks for your reply Karl! That worked. So what if I were to put 3 combo boxes on my parameter form, one for Description, Product, and Dosage. Is it possible to be able to choose All for all 3, or All for Description and an actual Product and Dosage from their respective tables and still have the data show up correctly? There are 9 different combinations for this so how could I go about writing that query. Assuming Product and Dosage are set up the exact same way as Description. Any thoughts? Thanks again! KARL DEWEY wrote: >Try now -- >SELECT Study.Description, Study.Product, Study.Dosage >FROM Study >WHERE (Study.Description = [forms]![SelectTrial]![txtDescription]) OR >(Study.Description like IIF([forms]![SelectTrial]![txtDescription] = "ALL", >"*",)); > >If you still get syntax error remove part of WHERE statement like this -- >SELECT Study.Description, Study.Product, Study.Dosage >FROM Study >WHERE (Study.Description = [forms]![SelectTrial]![txtDescription]); > or this -- >SELECT Study.Description, Study.Product, Study.Dosage >FROM Study >WHERE (Study.Description like IIF([forms]![SelectTrial]![txtDescription] = >"ALL", "*",)) > >> I'm getting syntax errors with your query. >> I took out an extra ) in the last line but it still errored. I deleted the >[quoted text clipped - 18 lines] >> >> >> >> Please help, thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201002/1 |