Report Parameter not displaying correct results

[gmazza via AccessMonster.com]

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!

 

[KARL DEWEY]

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", "*",))

 

[gmazza via AccessMonster.com]

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!

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!

 

[KARL DEWEY]

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.

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!

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!

 

[gmazza via AccessMonster.com]

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!

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]

 

[gmazza via AccessMonster.com]

As per my other posts, I need 3 combo boxes on my parameter form and I need
to be able to select ALL for either of them, or a combination of ALL and
other choices and have my report run with the correct data.
Can anyone lend me a hand to get this going?
I have it working for 1 but when I try for all 3 I am running into all kinds
of trouble. I did the same thing for the next 2 as I did for the first one
but it is not working.
Thanks in advance!

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!

Try now --
SELECT Study.Description, Study.Product, Study.Dosage

[quoted text clipped - 18 lines]

 

[Duane Hookom]

I typically use code to build a WHERE CONDITION for the docmd.openreport
method. IE:

Dim strWhere as String
strWhere = "1= 1"
If Me.txtDescription <> "ALL" Then
strWhere = strWhere & " AND [Description]=""" & Me.txtDescription & """ "
End If
If Me.txtProduct<> "ALL" Then
strWhere = strWhere & " AND [Product]=""" & Me.txtProduct& """ "
End If
If Me.txtDosage <> "ALL" Then
strWhere = strWhere & " AND [Dosage]=""" & Me.txtDosage & """ "
End If
DoCmd.OpenReport "rptYourRptName", acViewPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP

As per my other posts, I need 3 combo boxes on my parameter form and I need
to be able to select ALL for either of them, or a combination of ALL and
other choices and have my report run with the correct data.
Can anyone lend me a hand to get this going?
I have it working for 1 but when I try for all 3 I am running into all kinds
of trouble. I did the same thing for the next 2 as I did for the first one
but it is not working.
Thanks in advance!

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!

Try now --
SELECT Study.Description, Study.Product, Study.Dosage

[quoted text clipped - 18 lines]

Please help, thanks!

--



.