compound iif query

I have the following query
= "SELECT * FROM "
& IIf(Parameters!ar.Value = "renewal", "vwRenewal", "vwOPL"
& " Where 0=0 "
& IIf(Parameters!ar.value = "renewal", "AND (IIf(boolExtension = 1),
(dateEXTENSIONDATE >= '" & Parameters!begindate.Value & "') AND
(dateEXTENSIONDATE <= '" & Parameters!enddate.Value & "'), "")", "")
There is a lot more to this query, but basicaly everything works except the
above line. I want to say that if the ar=renewal and the boolextension is 1
then choose those dates, ortherwise choose everything as it should be. what
is wrong with this line I am getting an incorrect syntax error.The best way to track this down is to assign this to a textbox and see what
you get (I sometimes create a barebones report with only the parameters and
the textbox, no datasets). That is how I handle the problem.
Bruce L-C
"MallTLC" <MallTLC@.discussions.microsoft.com> wrote in message
news:FBC8D622-F1FD-40E2-AE97-12D9CB0EB608@.microsoft.com...
> I have the following query
> = "SELECT * FROM "
> & IIf(Parameters!ar.Value = "renewal", "vwRenewal", "vwOPL"
> & " Where 0=0 "
> & IIf(Parameters!ar.value = "renewal", "AND (IIf(boolExtension = 1),
> (dateEXTENSIONDATE >= '" & Parameters!begindate.Value & "') AND
> (dateEXTENSIONDATE <= '" & Parameters!enddate.Value & "'), "")", "")
> There is a lot more to this query, but basicaly everything works except
the
> above line. I want to say that if the ar=renewal and the boolextension is
1
> then choose those dates, ortherwise choose everything as it should be.
what
> is wrong with this line I am getting an incorrect syntax error.|||So I should be able to create compound iifs on the query page, correct.
"Bruce Loehle-Conger" wrote:
> The best way to track this down is to assign this to a textbox and see what
> you get (I sometimes create a barebones report with only the parameters and
> the textbox, no datasets). That is how I handle the problem.
> Bruce L-C
> "MallTLC" <MallTLC@.discussions.microsoft.com> wrote in message
> news:FBC8D622-F1FD-40E2-AE97-12D9CB0EB608@.microsoft.com...
> > I have the following query
> >
> > = "SELECT * FROM "
> >
> > & IIf(Parameters!ar.Value = "renewal", "vwRenewal", "vwOPL"
> >
> > & " Where 0=0 "
> >
> > & IIf(Parameters!ar.value = "renewal", "AND (IIf(boolExtension = 1),
> > (dateEXTENSIONDATE >= '" & Parameters!begindate.Value & "') AND
> > (dateEXTENSIONDATE <= '" & Parameters!enddate.Value & "'), "")", "")
> >
> > There is a lot more to this query, but basicaly everything works except
> the
> > above line. I want to say that if the ar=renewal and the boolextension is
> 1
> > then choose those dates, ortherwise choose everything as it should be.
> what
> > is wrong with this line I am getting an incorrect syntax error.
>
>|||Anything you can do with an expression you can do anywhere an expression is
used. Compounded iif's are 100% fine to do. They are confusing but fine. The
expression evaluation is no different if you are using it for the source of
a dataset or you are using it as the source of a textbox. So you can cut and
paste your expression into the source for a textbox. Right mouse click on
textbox, expression. Then paste the expression into the right side.
Bruce L-C
"MallTLC" <MallTLC@.discussions.microsoft.com> wrote in message
news:52404738-D383-4E63-B36A-23BE6E6B4EFB@.microsoft.com...
> So I should be able to create compound iifs on the query page, correct.
> "Bruce Loehle-Conger" wrote:
> > The best way to track this down is to assign this to a textbox and see
what
> > you get (I sometimes create a barebones report with only the parameters
and
> > the textbox, no datasets). That is how I handle the problem.
> >
> > Bruce L-C
> >
> > "MallTLC" <MallTLC@.discussions.microsoft.com> wrote in message
> > news:FBC8D622-F1FD-40E2-AE97-12D9CB0EB608@.microsoft.com...
> > > I have the following query
> > >
> > > = "SELECT * FROM "
> > >
> > > & IIf(Parameters!ar.Value = "renewal", "vwRenewal", "vwOPL"
> > >
> > > & " Where 0=0 "
> > >
> > > & IIf(Parameters!ar.value = "renewal", "AND (IIf(boolExtension = 1),
> > > (dateEXTENSIONDATE >= '" & Parameters!begindate.Value & "') AND
> > > (dateEXTENSIONDATE <= '" & Parameters!enddate.Value & "'), "")", "")
> > >
> > > There is a lot more to this query, but basicaly everything works
except
> > the
> > > above line. I want to say that if the ar=renewal and the
boolextension is
> > 1
> > > then choose those dates, ortherwise choose everything as it should be.
> > what
> > > is wrong with this line I am getting an incorrect syntax error.
> >
> >
> >