Dynamic Query problem

I am trying to create a dynamic query and add a 'ALL' category to my dropdown list, exactly like the functionality in the BOL walkthrough.
However I keep getting the "[BC30201]Expression Expected" error.

My query BEFORE attemting the dynamic query looks like this (much of the query is omitted)


....
AND t567.c536870970 = masterSr
AND t567.c536870970 in (SELECT c536870923 from t566 where (c536870922 = ?))
order by msrid

This works fine before making the query dynamic.

I then add 'All' to my dropdown parameter (Parameter1) like below:


SELECT 'All' AS Sponsor FROM t558
UNION
SELECT c536870917 AS Sponsor FROM t558 GROUP BY c536870917

The dropdown now has 'All' added to it. no problems.
But when I try to make the query dynamic, I get errors.
This is the new query:


="SELECT .....
AND t567.c536870970 = masterSr AND t567.c536870970 in (SELECT c536870923 from t566" & IIF(Parameters!Parameter1.Value = 'All', ""," where (c536870922 = " & Parameters!Parameter1.Value & "))") & " order by msrid"

As the BOL says, I have taken all carriage returns and spaces out and I'm still getting this error.
I am wondering if anyone sees anything that I am doing wrong, and better yet, Is there a better/easier way to do dynamic querying than this ?

Thanks for any help !

Does this work

="SELECT .....
AND t567.c536870970 = masterSr AND t567.c536870970 in
(SELECT c536870923 from t566" &
IIF(Parameters!Parameter1.Value = "All",
")",
" where c536870922 = ?)") &
" order by msrid"

Remove the linebreaks, I just though they might make the bits standout better

HTH

Jasper Smith

Great ! I didn't know you could do it that way. works great.
thanks Jasper!

Ok, call me stupid but I don't see what was removed from Dougwood's query string to Jasper's replacement...

<sigh>