Problems with conditional format - DATEADD function

I've been struggling with this problem for over a week now. I have a report
that gives me product renewal dates, in the table I want to highlight rows
depending on what month the renewal date falls into.
Sound pretty straightforward but its been anything but!
In my query I select the renewal dates I want using: >= DATEADD(mm, 1,
GETDATE()) AND < DATEADD(mm, 4, GETDATE()) which works fine.
However when I use the same DATEADD statement to conditionally format the
table I get the error: Argument 'Interval' is not a valid value. For some
reason it doesn't like anything I put for the Month interval in the
expression. I've tried every permatation I can think of for month including
putting "" round it but it doesn't like it - code is below
=IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 2, Today()),
"Yellow",
IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 3, Today()),
"Green",
IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 4, Today()),
"Blue", "Gray"
)))
Any help would be really appreciated!!!On Jan 14, 6:05=A0am, Jack <J...@.discussions.microsoft.com> wrote:
> I've been struggling with this problem for over a week now. I have a repor=t
> that gives me product renewal dates, in the table I want to highlight rows=
> depending on what month the renewal date falls into.
> Sound pretty straightforward but its been anything but!
> In my query I select the renewal dates I want using: >=3D DATEADD(mm, 1,
> GETDATE()) AND < DATEADD(mm, 4, GETDATE()) which works fine.
> However when I use the same DATEADD statement to conditionally format the
> table I get the error: Argument 'Interval' is not a valid value. For some
> reason it doesn't like anything I put for the Month interval in the
> expression. I've tried every permatation I can think of for month includin=g
> putting "" round it but it doesn't like it - code is below
> =3DIIF (Fields!New_RenewalDate.Value < DATEADD("mm", 2, Today()),
> "Yellow",
> IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 3, Today()),
> "Green",
> IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 4, Today()),
> "Blue", "Gray"
> )))
> Any help would be really appreciated!!!
Try DateInterval.Month instead of "mm" e.g. DateAdd(DateInterval.Month,
2,Today())
That should do the trick.
Happy reporting!
toolman|||I actually found the answer elsewhere - I should have been using "m" instead
of "mm".
Haven't tried your solution but I'm sure it works so thanks as well!
"toolman" wrote:
> On Jan 14, 6:05 am, Jack <J...@.discussions.microsoft.com> wrote:
> > I've been struggling with this problem for over a week now. I have a report
> > that gives me product renewal dates, in the table I want to highlight rows
> > depending on what month the renewal date falls into.
> >
> > Sound pretty straightforward but its been anything but!
> >
> > In my query I select the renewal dates I want using: >= DATEADD(mm, 1,
> > GETDATE()) AND < DATEADD(mm, 4, GETDATE()) which works fine.
> >
> > However when I use the same DATEADD statement to conditionally format the
> > table I get the error: Argument 'Interval' is not a valid value. For some
> > reason it doesn't like anything I put for the Month interval in the
> > expression. I've tried every permatation I can think of for month including
> > putting "" round it but it doesn't like it - code is below
> >
> > =IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 2, Today()),
> > "Yellow",
> > IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 3, Today()),
> > "Green",
> > IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 4, Today()),
> > "Blue", "Gray"
> > )))
> >
> > Any help would be really appreciated!!!
> Try DateInterval.Month instead of "mm" e.g. DateAdd(DateInterval.Month,
> 2,Today())
> That should do the trick.
> Happy reporting!
> toolman
>