Help with an expression

The error is:
The value expression for the textbox â'BillAddressâ' contains an error:
[BC30201] Expression expected.
=If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr (10) +
Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.Value) = 0 AND
Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line2.Value) = 0 AND
Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.Value)= 0 AND
Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.value)= 0 AND
Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
Fields!AcctName.Value + Chr(10) +
"No Address Available" + Chr(10) +
"ID: " + AccountNumber
Many ThanksJeff,
I thought VB was the scripting language to be used in expressions. Why
are you concatinating with '+' operators, when you should be using '&'
operators?
thx
-jsh
"Jeff Metcalf" wrote:
> The error is:
> The value expression for the textbox â'BillAddressâ' contains an error:
> [BC30201] Expression expected.
>
> =If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
> And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr (10) +
> Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.Value) = 0 AND
> Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
>
> Else If Len(Fields!BillTo_Line2.Value) = 0 AND
> Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.Value)= 0 AND
> Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.value)= 0 AND
> Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
> Fields!AcctName.Value + Chr(10) +
> "No Address Available" + Chr(10) +
> "ID: " + AccountNumber
> Many Thanks|||Beats me...I'm just trying to get it to work. The '+' operator works for
for concatinating in other SRS reports and in SQL. I'm not a VB or .Net
programmer, so I have no idea if it's right or not, lol.
"jsh02_nova@.hotmail.com" wrote:
> Jeff,
> I thought VB was the scripting language to be used in expressions. Why
> are you concatinating with '+' operators, when you should be using '&'
> operators?
> thx
> -jsh
> "Jeff Metcalf" wrote:
> > The error is:
> >
> > The value expression for the textbox â'BillAddressâ' contains an error:
> > [BC30201] Expression expected.
> >
> >
> > =If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
> > And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr (10) +
> > Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > > 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > > 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.Value) = 0 AND
> > Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> >
> > Else If Len(Fields!BillTo_Line2.Value) = 0 AND
> > Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.Value)= 0 AND
> > Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.value)= 0 AND
> > Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > "No Address Available" + Chr(10) +
> > "ID: " + AccountNumber
> >
> > Many Thanks|||I'm not sure if this is what's causing the problem but SRS uses an
"inline if" -IIF- instead of IF THEN ELSE.
The syntax is =IIF(test expression,true action, false action).
Using your expression from above it would be something like this:
=IIf( Len(Fields!BillTo_Line1.Value)= 0 And
Len(Fields!BillTo_Line2.Value) > 0
And Len(Fields!BillTo_Line3.Value) > 0 ,
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr (10) +
Fields!BillTo_City.value + ", " +
Fields!BillTo_StateOrProvince.Value + " " +
Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value ,
IIF( Len(Fields!BillTo_Line2.Value)= 0 And
Len(Fields!BillTo_Line1.Value) > 0 And Len(Fields!BillTo_Line3.Value) >
0 , and so on
Nested IIFs can get really ugly. If I were you and if it's possible
I'd do as much as you can with case statements in SQL.
Good luck

Help Required Regarding Formatting of Fields

Hello,
I am using SQL SERVER Reporting Services 2005. I am having a formatting problem. The problem is that I hav a textbox in which numeric field is coming. I want to show the currency format of the Regional Setting of the user pc. I have set the Language setting of the textbox to Default. Now when I am changing the Regional setting from US to UK then in the criteria section the datetime criteria (calendar's value) is changed according to the UK setting, but in the report section the selected date time is not changed (coming in US setting). And the currecy format is also not changing to UK format. Any help ?

Regards,

FurqanSet the format to =User!Language instead of default. I think that will work