I have a long expression that returns the value of my one label. The expression is suppose to figure out if there are three parts to the MailAddress field and return the appriopriate number of fields properly formated. The format I need to get returned from this delimited field looks like
PersonIdNumber
CO Line
Name Here
123 Address Street
City State Zip Country
Each line is followed by a semicolon which I have coded to each be replaced by a newline. The main problem is that sometimes we have a CO Line and sometimes we don't. There will be a delimeter right before the AddressStreet Line even if the CO Line does not exist, so that shouldn't be a problem.
Also, the Name Here line is not returned from my delimited MailAddress field, it is a seperate field. For some reason the label returns an error in the report. I'm assuming it is it is in the code below. If you see something wrong with the expression please let me know.
Code Snippet
=IIF(Count(Split(Fields!MailingAddress.Value, ";")) = 3, (LTrim(Replace(Replace(Choose(1, Split(Fields!MailingAddress.Value, ",")),",", " "), ";", " "))& vbcrlf & Fields!Name.Value & vbcrlf & LTrim(Choose(2, Split(Replace(Replace(Fields!MailingAddress.Value, ";", vbcrlf), ",", " "), vbcrlf)))& vbcrlf & LTrim(Choose(3, Split(Replace(Replace(Fields!MailingAddress.Value, ";", vbcrlf), ",", " "), vbcrlf)))), (Fields!Name.Value & vbcrlf & LTrim(Choose(1, Split(Replace(Replace(Fields!MailingAddress.Value, ";", vbcrlf), ",", " "), vbcrlf)))& vbcrlf & LTrim(Choose(2, Split(Replace(Replace(Fields!MailingAddress.Value, ";", vbcrlf), ",", " "), vbcrlf)))))
I'm currently using SSRS 2005...
Hello,
What's the error you're getting when you add the Name field to the expression?
Jarret
|||
The error that is returned is for the entire expression (Name field included). The error just looks like your usual label error :
#Error
|||
Hello,
If you open the Output window (Ctrl+Alt+O), you should see the actual error message you are getting.
Jarret
|||
It is the rsRuntimeErrorinExpression:
The Value expression for the textbox ‘textbox1’ contains an error: Operation is not valid due to the current state of the object.
Preview complete -- 0 errors, 1 warnings
|||
Here is a couple of examples of the MailAddress field coming straight from our database:
With CO Line...
RE: 2423 N. First St.;,9838 Witicki Road;,Sandsburg,Ohio,23911;
Without CO Line..
;,9838 Witicki Road;,Sandsburg,Ohio,23911;
Hope this helps!
|||
Hello,
The problem was with the Count function call, you can't use it on an array. From your data examples, it looks like you will always have 3 semi-colons to seperate your data (whether or not it has data for that section).
Try this:
Code Snippet
=LTrim(Replace(Switch(Len(Split(Fields!MailingAddress.Value, ";")(0)) > 0, Split(Fields!MailingAddress.Value, ";")(0) & vbCrLf), ",", " "))
& Fields!Name.Value
& vbCrLf & LTrim(Replace(Split(Fields!MailingAddress.Value, ";")(1), ",", " "))
& vbCrLf & LTrim(Replace(Split(Fields!MailingAddress.Value, ";")(2), ",", " "))
Hope this helps.
Jarret
|||
Thanks for the quick replies! I tried this and it works with the CO Line, but not without. It chooses the address line to go above the Name field instead of nothing. It looked like this:
9838 Witicki Road
Dan Ricksworth
Sandsburg Ohio 23911
Instead of looking like:
Dan Ricksworth
9838 Witicki Road
Sandsburg Ohio 23911
I have decided to edit our database's MailAddress function that collects and adds the delimeters. I'm going to add the Name field into it and then just use the replace function to add the newlines. Hopefully this will work.
|||
I'm a little confused, I used the example data you supplied and got this as the result:
| Mailing Address | Name | Result |
| RE: 2423 N. First St.;,9838 Witicki Road;,Sandsburg,Ohio,23911; | Bob Ricksworth | RE: 2423 N. First St.
Bob Ricksworth
9838 Witicki Road
Sandsburg Ohio 23911 |
| ;,9838 Witicki Road;,Sandsburg,Ohio,23911; | Dan Ricksworth | Dan Ricksworth
9838 Witicki Road
Sandsburg Ohio 23911 |
Is this not the correct format? If the first section of the MailingAddress has a value, then the name is listed 2nd, otherwise, the name is listed first. In the above table, the first row has the CO Line (marked in red), and the second row does not have a CO Line.
This is the expression I used:
Code Snippet
=LTrim(Replace(Switch(Len(Split(Fields!MailingAddress.Value, ";")(0)) > 0, Split(Fields!MailingAddress.Value, ";")(0) & vbCrLf), ",", " "))
& Fields!Name.Value
& vbCrLf & LTrim(Replace(Split(Fields!MailingAddress.Value, ";")(1), ",", " "))
& vbCrLf & LTrim(Replace(Split(Fields!MailingAddress.Value, ";")(2), ",", " "))
Jarret
|||Thats weird... I copied and pasted your expression directly into my label and it gave me the wrong format. It probably is my fault. I'm assuming that somewhere I missed a delimeter (either comma or semi-colon) and gave you incorrect data from MailingAddress. Your expression looks right. I'll give you the solution. At this point I have already added the Name field to the MailingAddress field and have got the correct format. It would be too time consuming to go back and test to figure out where I went wrong. Thanks for the help though!|||
Yes, that is weird that it showed differently. Oh well, glad to help!
Jarret
