datediff() alters other values in nested iif

i have a nested IIF statement, see below, that evaluates all possible field
values of a particular field, and outputs appropriate text. The possible
field values for Fields!STYLESEASON, are
"FLASH","BASIC" and text consisting of year and month in "yymm" format. e.g.
"0604"
I convert the last possible value type to date by concatenation:
CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
The report works fine like this , but once I introduce the DATEDIFF(),
DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
generating a invalid date function. In other words, instead of the values
"FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
suitable values that can be converted to a date format, they too are
subjected to CDATE(). You can clearly see this in the error below as "BASIC"
is converted to "01/IC/BA"
complete IIF expression:
=IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))= "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
< -2,"OLD","FASHION")))
error generated when DATEDIFF() is introduced:
Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textbox
â'textbox25â' contains an error: Conversion from string "01/IC/BA" to type
'Date' is not valid.
thanks for you help in advance.
anthonyinstead of using cdate, do you think datevalue may work better?
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>i have a nested IIF statement, see below, that evaluates all possible field
> values of a particular field, and outputs appropriate text. The possible
> field values for Fields!STYLESEASON, are
> "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> e.g.
> "0604"
> I convert the last possible value type to date by concatenation:
> CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> The report works fine like this , but once I introduce the DATEDIFF(),
> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> generating a invalid date function. In other words, instead of the values
> "FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
> suitable values that can be converted to a date format, they too are
> subjected to CDATE(). You can clearly see this in the error below as
> "BASIC"
> is converted to "01/IC/BA"
>
> complete IIF expression:
> =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> < -2,"OLD","FASHION")))
>
> error generated when DATEDIFF() is introduced:
> Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> textbox
> 'textbox25' contains an error: Conversion from string "01/IC/BA" to type
> 'Date' is not valid.
> thanks for you help in advance.
> anthony
>
>|||thanks for the quick reply...cdate is not the issue it's the introduction of
datediff that appears to be called prior to the earlier conditions in the
nested iif being evaluated. as a result, the date expression is evaluated on
data that should have been accounted for before...see the error message
posted "01/IC/BA" will never be recognized as a date no matter what function
i call.
"Ben Watts" wrote:
> instead of using cdate, do you think datevalue may work better?
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >i have a nested IIF statement, see below, that evaluates all possible field
> > values of a particular field, and outputs appropriate text. The possible
> > field values for Fields!STYLESEASON, are
> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> > e.g.
> > "0604"
> >
> > I convert the last possible value type to date by concatenation:
> >
> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> > The report works fine like this , but once I introduce the DATEDIFF(),
> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> > generating a invalid date function. In other words, instead of the values
> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
> > suitable values that can be converted to a date format, they too are
> > subjected to CDATE(). You can clearly see this in the error below as
> > "BASIC"
> > is converted to "01/IC/BA"
> >
> >
> > complete IIF expression:
> >
> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> > < -2,"OLD","FASHION")))
> >
> >
> > error generated when DATEDIFF() is introduced:
> >
> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> > textbox
> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to type
> > 'Date' is not valid.
> >
> > thanks for you help in advance.
> > anthony
> >
> >
> >
> >
>
>|||What are the values of styleseason, that you are trying to convert? So far
I know there is flash and basic, but what are the others?
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> thanks for the quick reply...cdate is not the issue it's the introduction
> of
> datediff that appears to be called prior to the earlier conditions in the
> nested iif being evaluated. as a result, the date expression is evaluated
> on
> data that should have been accounted for before...see the error message
> posted "01/IC/BA" will never be recognized as a date no matter what
> function
> i call.
> "Ben Watts" wrote:
>> instead of using cdate, do you think datevalue may work better?
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >i have a nested IIF statement, see below, that evaluates all possible
>> >field
>> > values of a particular field, and outputs appropriate text. The
>> > possible
>> > field values for Fields!STYLESEASON, are
>> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
>> > e.g.
>> > "0604"
>> >
>> > I convert the last possible value type to date by concatenation:
>> >
>> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> > The report works fine like this , but once I introduce the DATEDIFF(),
>> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
>> > generating a invalid date function. In other words, instead of the
>> > values
>> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
>> > only
>> > suitable values that can be converted to a date format, they too are
>> > subjected to CDATE(). You can clearly see this in the error below as
>> > "BASIC"
>> > is converted to "01/IC/BA"
>> >
>> >
>> > complete IIF expression:
>> >
>> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> > < -2,"OLD","FASHION")))
>> >
>> >
>> > error generated when DATEDIFF() is introduced:
>> >
>> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
>> > textbox
>> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
>> > type
>> > 'Date' is not valid.
>> >
>> > thanks for you help in advance.
>> > anthony
>> >
>> >
>> >
>> >
>>|||from my original post:
-- The possible
field values for Fields!STYLESEASON, are
"FLASH","BASIC" and text consisting of year and month in "yymm" format. e.g.
"0604"--
I am not trying to convert all values only the ones that are not flash or
basic. The other values,which are in yymm format I am doing some string
manipulation and concatenation to get it into a mm/dd/yy format.
"01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
According to my IIF statement I am evaluating conditions for flash and basic
first wich should leave only yymm to convert to mm/dd/yy values. At this
point the report works as it should
BASIC to BASIC
FLASH to OLD
yymm to mm/dd/yy
Once I introduce any date function cdate,dateval or datediff into a sinlge
IIF in the nested IIF statements all styleseason values are evaluated by the
date function and obviously gives an error for the flash and basic values. I
end up with values like
"01/IC/BA" trying to be evaluated, which is BASIC run through the
concatenation. Please see full IIF statement.
CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
"Ben Watts" wrote:
> What are the values of styleseason, that you are trying to convert? So far
> I know there is flash and basic, but what are the others?
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> > thanks for the quick reply...cdate is not the issue it's the introduction
> > of
> > datediff that appears to be called prior to the earlier conditions in the
> > nested iif being evaluated. as a result, the date expression is evaluated
> > on
> > data that should have been accounted for before...see the error message
> > posted "01/IC/BA" will never be recognized as a date no matter what
> > function
> > i call.
> >
> > "Ben Watts" wrote:
> >
> >> instead of using cdate, do you think datevalue may work better?
> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >> >i have a nested IIF statement, see below, that evaluates all possible
> >> >field
> >> > values of a particular field, and outputs appropriate text. The
> >> > possible
> >> > field values for Fields!STYLESEASON, are
> >> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> >> > e.g.
> >> > "0604"
> >> >
> >> > I convert the last possible value type to date by concatenation:
> >> >
> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >
> >> > The report works fine like this , but once I introduce the DATEDIFF(),
> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >
> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> >> > generating a invalid date function. In other words, instead of the
> >> > values
> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
> >> > only
> >> > suitable values that can be converted to a date format, they too are
> >> > subjected to CDATE(). You can clearly see this in the error below as
> >> > "BASIC"
> >> > is converted to "01/IC/BA"
> >> >
> >> >
> >> > complete IIF expression:
> >> >
> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> > < -2,"OLD","FASHION")))
> >> >
> >> >
> >> > error generated when DATEDIFF() is introduced:
> >> >
> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> >> > textbox
> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
> >> > type
> >> > 'Date' is not valid.
> >> >
> >> > thanks for you help in advance.
> >> > anthony
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||I see a wayof trying it. You could either write some sort of CASE statment
in your select statement setting the value to a field. Like:
CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as Date
Then write your nested if, something like this.
iif(Fields!Date.Value = 'neither' and
DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
< -2,
'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
I think you see where I am taking this. Basically the case statment will be
handled first then the rest of it will also be handled in turn. I hope this
helps.
Then enter that instead of your nested if and that should work.
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
> from my original post:
> -- The possible
> field values for Fields!STYLESEASON, are
> "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> e.g.
> "0604"--
> I am not trying to convert all values only the ones that are not flash or
> basic. The other values,which are in yymm format I am doing some string
> manipulation and concatenation to get it into a mm/dd/yy format.
> "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
> According to my IIF statement I am evaluating conditions for flash and
> basic
> first wich should leave only yymm to convert to mm/dd/yy values. At this
> point the report works as it should
> BASIC to BASIC
> FLASH to OLD
> yymm to mm/dd/yy
> Once I introduce any date function cdate,dateval or datediff into a
> sinlge
> IIF in the nested IIF statements all styleseason values are evaluated by
> the
> date function and obviously gives an error for the flash and basic values.
> I
> end up with values like
> "01/IC/BA" trying to be evaluated, which is BASIC run through the
> concatenation. Please see full IIF statement.
> CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>
>
>
> "Ben Watts" wrote:
>> What are the values of styleseason, that you are trying to convert? So
>> far
>> I know there is flash and basic, but what are the others?
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
>> > thanks for the quick reply...cdate is not the issue it's the
>> > introduction
>> > of
>> > datediff that appears to be called prior to the earlier conditions in
>> > the
>> > nested iif being evaluated. as a result, the date expression is
>> > evaluated
>> > on
>> > data that should have been accounted for before...see the error message
>> > posted "01/IC/BA" will never be recognized as a date no matter what
>> > function
>> > i call.
>> >
>> > "Ben Watts" wrote:
>> >
>> >> instead of using cdate, do you think datevalue may work better?
>> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >> >i have a nested IIF statement, see below, that evaluates all possible
>> >> >field
>> >> > values of a particular field, and outputs appropriate text. The
>> >> > possible
>> >> > field values for Fields!STYLESEASON, are
>> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
>> >> > format.
>> >> > e.g.
>> >> > "0604"
>> >> >
>> >> > I convert the last possible value type to date by concatenation:
>> >> >
>> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >
>> >> > The report works fine like this , but once I introduce the
>> >> > DATEDIFF(),
>> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >
>> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
>> >> > generating a invalid date function. In other words, instead of the
>> >> > values
>> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
>> >> > only
>> >> > suitable values that can be converted to a date format, they too are
>> >> > subjected to CDATE(). You can clearly see this in the error below
>> >> > as
>> >> > "BASIC"
>> >> > is converted to "01/IC/BA"
>> >> >
>> >> >
>> >> > complete IIF expression:
>> >> >
>> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> > < -2,"OLD","FASHION")))
>> >> >
>> >> >
>> >> > error generated when DATEDIFF() is introduced:
>> >> >
>> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
>> >> > textbox
>> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
>> >> > type
>> >> > 'Date' is not valid.
>> >> >
>> >> > thanks for you help in advance.
>> >> > anthony
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||i was actually trying to avoid doing any of the cases in sql, but it looks
like ill have to do it that way. don't you think it is a bug of some sort as
to why calling the function in the iif takes precedence over the previous
conditional statements. in any regards, thank you for your time and help.
"Ben Watts" wrote:
> I see a wayof trying it. You could either write some sort of CASE statment
> in your select statement setting the value to a field. Like:
> CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
> WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as Date
> Then write your nested if, something like this.
> iif(Fields!Date.Value = 'neither' and
> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> < -2,
> 'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
> I think you see where I am taking this. Basically the case statment will be
> handled first then the rest of it will also be handled in turn. I hope this
> helps.
>
> Then enter that instead of your nested if and that should work.
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
> > from my original post:
> > -- The possible
> > field values for Fields!STYLESEASON, are
> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> > e.g.
> > "0604"--
> >
> > I am not trying to convert all values only the ones that are not flash or
> > basic. The other values,which are in yymm format I am doing some string
> > manipulation and concatenation to get it into a mm/dd/yy format.
> >
> > "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
> >
> > According to my IIF statement I am evaluating conditions for flash and
> > basic
> > first wich should leave only yymm to convert to mm/dd/yy values. At this
> > point the report works as it should
> >
> > BASIC to BASIC
> > FLASH to OLD
> > yymm to mm/dd/yy
> >
> > Once I introduce any date function cdate,dateval or datediff into a
> > sinlge
> > IIF in the nested IIF statements all styleseason values are evaluated by
> > the
> > date function and obviously gives an error for the flash and basic values.
> > I
> > end up with values like
> > "01/IC/BA" trying to be evaluated, which is BASIC run through the
> > concatenation. Please see full IIF statement.
> >
> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> >
> >
> >
> >
> >
> > "Ben Watts" wrote:
> >
> >> What are the values of styleseason, that you are trying to convert? So
> >> far
> >> I know there is flash and basic, but what are the others?
> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> >> > thanks for the quick reply...cdate is not the issue it's the
> >> > introduction
> >> > of
> >> > datediff that appears to be called prior to the earlier conditions in
> >> > the
> >> > nested iif being evaluated. as a result, the date expression is
> >> > evaluated
> >> > on
> >> > data that should have been accounted for before...see the error message
> >> > posted "01/IC/BA" will never be recognized as a date no matter what
> >> > function
> >> > i call.
> >> >
> >> > "Ben Watts" wrote:
> >> >
> >> >> instead of using cdate, do you think datevalue may work better?
> >> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >> >> >i have a nested IIF statement, see below, that evaluates all possible
> >> >> >field
> >> >> > values of a particular field, and outputs appropriate text. The
> >> >> > possible
> >> >> > field values for Fields!STYLESEASON, are
> >> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
> >> >> > format.
> >> >> > e.g.
> >> >> > "0604"
> >> >> >
> >> >> > I convert the last possible value type to date by concatenation:
> >> >> >
> >> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> >
> >> >> > The report works fine like this , but once I introduce the
> >> >> > DATEDIFF(),
> >> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> >
> >> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> >> >> > generating a invalid date function. In other words, instead of the
> >> >> > values
> >> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
> >> >> > only
> >> >> > suitable values that can be converted to a date format, they too are
> >> >> > subjected to CDATE(). You can clearly see this in the error below
> >> >> > as
> >> >> > "BASIC"
> >> >> > is converted to "01/IC/BA"
> >> >> >
> >> >> >
> >> >> > complete IIF expression:
> >> >> >
> >> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> >> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> > < -2,"OLD","FASHION")))
> >> >> >
> >> >> >
> >> >> > error generated when DATEDIFF() is introduced:
> >> >> >
> >> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> >> >> > textbox
> >> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
> >> >> > type
> >> >> > 'Date' is not valid.
> >> >> >
> >> >> > thanks for you help in advance.
> >> >> > anthony
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||It really does seem like a bug, you could always put that portion of the if
statement first and see if it works that way. I have had weirder things
happen with if statements. Good luck
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:A9BF9E34-15F4-43C5-980E-A6AF59605C16@.microsoft.com...
>i was actually trying to avoid doing any of the cases in sql, but it looks
> like ill have to do it that way. don't you think it is a bug of some sort
> as
> to why calling the function in the iif takes precedence over the previous
> conditional statements. in any regards, thank you for your time and help.
> "Ben Watts" wrote:
>> I see a wayof trying it. You could either write some sort of CASE
>> statment
>> in your select statement setting the value to a field. Like:
>> CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
>> WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as
>> Date
>> Then write your nested if, something like this.
>> iif(Fields!Date.Value = 'neither' and
>> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> < -2,
>> 'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
>> I think you see where I am taking this. Basically the case statment will
>> be
>> handled first then the rest of it will also be handled in turn. I hope
>> this
>> helps.
>>
>> Then enter that instead of your nested if and that should work.
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
>> > from my original post:
>> > -- The possible
>> > field values for Fields!STYLESEASON, are
>> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
>> > e.g.
>> > "0604"--
>> >
>> > I am not trying to convert all values only the ones that are not flash
>> > or
>> > basic. The other values,which are in yymm format I am doing some
>> > string
>> > manipulation and concatenation to get it into a mm/dd/yy format.
>> >
>> > "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
>> >
>> > According to my IIF statement I am evaluating conditions for flash and
>> > basic
>> > first wich should leave only yymm to convert to mm/dd/yy values. At
>> > this
>> > point the report works as it should
>> >
>> > BASIC to BASIC
>> > FLASH to OLD
>> > yymm to mm/dd/yy
>> >
>> > Once I introduce any date function cdate,dateval or datediff into a
>> > sinlge
>> > IIF in the nested IIF statements all styleseason values are evaluated
>> > by
>> > the
>> > date function and obviously gives an error for the flash and basic
>> > values.
>> > I
>> > end up with values like
>> > "01/IC/BA" trying to be evaluated, which is BASIC run through the
>> > concatenation. Please see full IIF statement.
>> >
>> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Ben Watts" wrote:
>> >
>> >> What are the values of styleseason, that you are trying to convert?
>> >> So
>> >> far
>> >> I know there is flash and basic, but what are the others?
>> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
>> >> > thanks for the quick reply...cdate is not the issue it's the
>> >> > introduction
>> >> > of
>> >> > datediff that appears to be called prior to the earlier conditions
>> >> > in
>> >> > the
>> >> > nested iif being evaluated. as a result, the date expression is
>> >> > evaluated
>> >> > on
>> >> > data that should have been accounted for before...see the error
>> >> > message
>> >> > posted "01/IC/BA" will never be recognized as a date no matter what
>> >> > function
>> >> > i call.
>> >> >
>> >> > "Ben Watts" wrote:
>> >> >
>> >> >> instead of using cdate, do you think datevalue may work better?
>> >> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >> >> >i have a nested IIF statement, see below, that evaluates all
>> >> >> >possible
>> >> >> >field
>> >> >> > values of a particular field, and outputs appropriate text. The
>> >> >> > possible
>> >> >> > field values for Fields!STYLESEASON, are
>> >> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
>> >> >> > format.
>> >> >> > e.g.
>> >> >> > "0604"
>> >> >> >
>> >> >> > I convert the last possible value type to date by concatenation:
>> >> >> >
>> >> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> >
>> >> >> > The report works fine like this , but once I introduce the
>> >> >> > DATEDIFF(),
>> >> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> >
>> >> >> > all instances of Fields!STYLESEASON.Value are evaluated by
>> >> >> > CDATE()
>> >> >> > generating a invalid date function. In other words, instead of
>> >> >> > the
>> >> >> > values
>> >> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions
>> >> >> > leaving
>> >> >> > only
>> >> >> > suitable values that can be converted to a date format, they too
>> >> >> > are
>> >> >> > subjected to CDATE(). You can clearly see this in the error
>> >> >> > below
>> >> >> > as
>> >> >> > "BASIC"
>> >> >> > is converted to "01/IC/BA"
>> >> >> >
>> >> >> >
>> >> >> > complete IIF expression:
>> >> >> >
>> >> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> >> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> > < -2,"OLD","FASHION")))
>> >> >> >
>> >> >> >
>> >> >> > error generated when DATEDIFF() is introduced:
>> >> >> >
>> >> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for
>> >> >> > the
>> >> >> > textbox
>> >> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA"
>> >> >> > to
>> >> >> > type
>> >> >> > 'Date' is not valid.
>> >> >> >
>> >> >> > thanks for you help in advance.
>> >> >> > anthony
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>