Address field problem.

I have a number of text boxes (one on top of the other) to display the addresses in my report. I have Address_name, Address1, Address2, Address_city, Address_State, Address_PostalCode - Each with its own text box except for the city, state, and postal code for which I just combined the fields. However, I have many who do not have any data for Address2 and don't want an empty line in the address. Is there an expression I can use to display the next line or move/shift up the remaining parts of the address? Would the iif and isnothing expressions be helpful, and if so, how do I write it correctly?

If it is ok to put them all into one textbox, you could do this:

=Fields!Address_name.Value
& chr(10) & Fields!Address1.Value
& IIf(Len(Fields!Address2.Value) > 0, chr(10) & Fields!Address2.Value, "")
& chr(10) & Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value

If they have to have their own textbox and you just want to move it up if the Address2 is empty, then you could try something like this.

In the expression for the Address2 textbox:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address2.Value, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value)

Then, in the expression for the City/State/Postal Code:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value, "")

Hope this helps.

Jarret

|||

Thanks mate, I'll give it a try.!

What does "Len" do?

|||

Len gives you then length of the object you pass in. In this case, if the Address2 is NULL or an empty string, Len will return 0 and it will be skipped with the logic in the code.

Let me know if that fixes your issue.

Jarret

|||

I see, thanks for the info. I will try this in about an hour or so, I'll post back and give you an update.

Thanks again Jarret,

Bill

Adding Values in a Text Box

I have multiple values in text boxes based on Summed values. I would like to
add the values that are in the text boxes. What is the ref for Boxes? I
know fields are Fields!.
Here is an example of what I am running it the text boxes. They are in the
group footer. I would like to add them in the report footer, which is in a
different scope.
=IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
Fields!ItemLoss.Value), 0)
--
Thank You, LeoI would like to sum the values not just add them.
Thanks
"TrussworksLeo" wrote:
> I have multiple values in text boxes based on Summed values. I would like to
> add the values that are in the text boxes. What is the ref for Boxes? I
> know fields are Fields!.
> Here is an example of what I am running it the text boxes. They are in the
> group footer. I would like to add them in the report footer, which is in a
> different scope.
> =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> Fields!ItemLoss.Value), 0)
> --
> Thank You, Leo|||Take a look a the ReportItems!<TextboxName>.Value syntax. This syntax allows
you to reference values in a textbox.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TrussworksLeo" <Leo@.noemail.noemail> wrote in message
news:509843C2-29B9-4B0E-B22A-5C497B99F0AB@.microsoft.com...
> I would like to sum the values not just add them.
> Thanks
> "TrussworksLeo" wrote:
> > I have multiple values in text boxes based on Summed values. I would
like to
> > add the values that are in the text boxes. What is the ref for Boxes?
I
> > know fields are Fields!.
> >
> > Here is an example of what I am running it the text boxes. They are in
the
> > group footer. I would like to add them in the report footer, which is
in a
> > different scope.
> >
> > =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> > Fields!ItemLoss.Value), 0)
> > --
> > Thank You, Leo|||Yes, But it will not allow me to us a aggregate like sum against it?
Leo
"Bruce Johnson [MSFT]" wrote:
> Take a look a the ReportItems!<TextboxName>.Value syntax. This syntax allows
> you to reference values in a textbox.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> news:509843C2-29B9-4B0E-B22A-5C497B99F0AB@.microsoft.com...
> > I would like to sum the values not just add them.
> >
> > Thanks
> >
> > "TrussworksLeo" wrote:
> >
> > > I have multiple values in text boxes based on Summed values. I would
> like to
> > > add the values that are in the text boxes. What is the ref for Boxes?
> I
> > > know fields are Fields!.
> > >
> > > Here is an example of what I am running it the text boxes. They are in
> the
> > > group footer. I would like to add them in the report footer, which is
> in a
> > > different scope.
> > >
> > > =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> > > Fields!ItemLoss.Value), 0)
> > > --
> > > Thank You, Leo
>
>

Adding Carriage Return and Line Feeds to a text box

Hi All,

I am trying to print 10 values in a single cell in a text box.....

To make it readable I put carriage returns and line feeds into the boxes.

These rendered ok in visual studio but did not render ok in explorer....no carriage return or line feed.

This is what I added...

=Fields!segment_sdesc_00.Value & chr(10) & chr(13)

Can anyone advise me as to what is wrong with this? There must be a way to put a cr/lf or newline into a report in a text box.....

Thanks in Advance

Peter

www.peternolan.com

Try using the + sign over the ampersand.

Code Snippet

=Fields!segment_sdesc_00.Value + chr(10) + chr(13)

This has worked for me in Explorer so far.

|||

If you are working with SRS and need to combine two CRM fields together, but having each on its own line, use the following expression:

=Fields!ShipToName.Value & vbCRLF & Fields!ShipToAddress.Value

vbCRLF is a special system constant that contains the values of carriage return and line feed characters.

- Mitch Milam’s Microsoft Discussions

|||

Hi All,

thanks for the tips...will try both....

Peter

|||

Hi Simone,

some of the fields are numbers and from memory it complained about the plus sign when there was a mixture of numeric and string fields...but I will try again.

Thanks

Peter