Formatting a field in a database

Hi,
I want to format an address field that is in a database as 123 Happy Street|
|Montgomery|AL|36104
I want to break it out into address1, address2, etc without the pipes.
I found some code that seems to fix my problem below pretty well, but about
half the time it doesn't work. I get an error message when I run it that
says - An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Cannot read the next data row for the data set DAIS.
(rsErrorReadingNextDataRow) Get Online Help
Invalid length parameter passed to the substring function.
The following is part my code. Can anybody PLEASE help me figure why this
doesn't always work or tell me a better way to do it?
Thanks
Jill
SELECT TOP 1 SUBSTRING(NAMEAttributes1.Attribute, 0, CHARINDEX('|',
NAMEAttributes1.Attribute, 0)) AS Addr1, SUBSTRING(NAMEAttributes1.Attribute,
CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1,
CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1)
- (CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1))
AS Addr2, SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute,
CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1,
CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute,
CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1)
- CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1) - 1)
AS City, SUBSTRING(NAMEAttributes1.Attribute,
CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1) + 1) + 1,
CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) - CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1) + 1) - 1) AS State,
SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|', NAMEAttributes1.Attribute,
CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) + 1, 5)
AS ZIP,Suppose that you wouldn't want to rethink how you are keeping your
address.....
Anyway, the code you have should work as long as you can absolutely
guarantee that your addresses will be in the desired format. If you are
missing one pipe as an example you will end up with the error message that
you supplied. I think that is probably your problem and that you need to
clean up the data.
Note that you could work around thie problem of missing pipes in a stored
proc except for the fact that if a pipe is missing there's no way to know
exactly what piece of data is missing. With a normal table structure holding
the address in separate fields this would not be an issue.
"Jill" wrote:
> Hi,
> I want to format an address field that is in a database as 123 Happy Street|
> |Montgomery|AL|36104
> I want to break it out into address1, address2, etc without the pipes.
> I found some code that seems to fix my problem below pretty well, but about
> half the time it doesn't work. I get an error message when I run it that
> says - An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> Cannot read the next data row for the data set DAIS.
> (rsErrorReadingNextDataRow) Get Online Help
> Invalid length parameter passed to the substring function.
> The following is part my code. Can anybody PLEASE help me figure why this
> doesn't always work or tell me a better way to do it?
> Thanks
> Jill
> SELECT TOP 1 SUBSTRING(NAMEAttributes1.Attribute, 0, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0)) AS Addr1, SUBSTRING(NAMEAttributes1.Attribute,
> CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1,
> CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1)
> - (CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1))
> AS Addr2, SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute,
> CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1,
> CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute,
> CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1)
> - CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1) - 1)
> AS City, SUBSTRING(NAMEAttributes1.Attribute,
> CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1) + 1) + 1,
> CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) - CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1) + 1) - 1) AS State,
> SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|', NAMEAttributes1.Attribute,
> CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) + 1, 5)
> AS ZIP,
>|||Perhaps you can use this code in Reporting Service.
=Split(Fields!Address.Value, "|")(0)
=Split(Fields!Address.Value, "|")(1)
=Split(Fields!Address.Value, "|")(2)
=Split(Fields!Address.Value, "|")(3)
Hope this helps ..
> "Jill" wrote:
> > Hi,
> > I want to format an address field that is in a database as 123 Happy Street|
> > |Montgomery|AL|36104
> > I want to break it out into address1, address2, etc without the pipes.
> >
> > I found some code that seems to fix my problem below pretty well, but about
> > half the time it doesn't work. I get an error message when I run it that
> > says - An error has occurred during report processing. (rsProcessingAborted)
> > Get Online Help
> > Cannot read the next data row for the data set DAIS.
> > (rsErrorReadingNextDataRow) Get Online Help
> > Invalid length parameter passed to the substring function.
> >
> > The following is part my code. Can anybody PLEASE help me figure why this
> > doesn't always work or tell me a better way to do it?
> >
> > Thanks
> > Jill
> >
> > SELECT TOP 1 SUBSTRING(NAMEAttributes1.Attribute, 0, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0)) AS Addr1, SUBSTRING(NAMEAttributes1.Attribute,
> > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1,
> > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1)
> > - (CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1))
> > AS Addr2, SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute,
> > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1,
> > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute,
> > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1)
> > - CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1) - 1)
> > AS City, SUBSTRING(NAMEAttributes1.Attribute,
> > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1,
> > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) - CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1) + 1) - 1) AS State,
> > SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|', NAMEAttributes1.Attribute,
> > CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) + 1, 5)
> > AS ZIP,
> >|||Thank You, Thank You!! This worked!!
"Keenan" wrote:
> Perhaps you can use this code in Reporting Service.
> =Split(Fields!Address.Value, "|")(0)
> =Split(Fields!Address.Value, "|")(1)
> =Split(Fields!Address.Value, "|")(2)
> =Split(Fields!Address.Value, "|")(3)
> Hope this helps ..
> > "Jill" wrote:
> >
> > > Hi,
> > > I want to format an address field that is in a database as 123 Happy Street|
> > > |Montgomery|AL|36104
> > > I want to break it out into address1, address2, etc without the pipes.
> > >
> > > I found some code that seems to fix my problem below pretty well, but about
> > > half the time it doesn't work. I get an error message when I run it that
> > > says - An error has occurred during report processing. (rsProcessingAborted)
> > > Get Online Help
> > > Cannot read the next data row for the data set DAIS.
> > > (rsErrorReadingNextDataRow) Get Online Help
> > > Invalid length parameter passed to the substring function.
> > >
> > > The following is part my code. Can anybody PLEASE help me figure why this
> > > doesn't always work or tell me a better way to do it?
> > >
> > > Thanks
> > > Jill
> > >
> > > SELECT TOP 1 SUBSTRING(NAMEAttributes1.Attribute, 0, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0)) AS Addr1, SUBSTRING(NAMEAttributes1.Attribute,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1)
> > > - (CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1))
> > > AS Addr2, SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1)
> > > - CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1) - 1)
> > > AS City, SUBSTRING(NAMEAttributes1.Attribute,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) - CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1) + 1) - 1) AS State,
> > > SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|', NAMEAttributes1.Attribute,
> > > CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) + 1, 5)
> > > AS ZIP,
> > >

Format an address

I need a little help in formatting the address field. We use a 3 or 4 line
address field at the top of a letter like this:
Name
Address 1
Address 2
CityStateZip
I want to put them all in one textbox but sometimes Address 2 is NULL and I
would like to supress the one line. The code looks like this:
=Fields!FullName.Value & vbcrlf & Fields!Line1.Value & vbcrlf &
Fields!Line2.Value & vbcrlf & Fields!CityStateZip.Value
Does anybody know how to supress the NULL line? Is there a better way to do
this?
--
ZipHow about something like
=iif(Fields!Line2.Value is Nothing,
Fields!FullName.Value & vbcrlf & Fields!Line1.Value & vbcrlf &
Fields!CityStateZip.Value,
Fields!FullName.Value & vbcrlf & Fields!Line1.Value & vbcrlf &
Fields!Line2.Value & vbcrlf & Fields!CityStateZip.Value )
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zip" <Zip@.discussions.microsoft.com> wrote in message
news:AA45AB20-23F9-4449-94D9-863B963FABB4@.microsoft.com...
>I need a little help in formatting the address field. We use a 3 or 4 line
> address field at the top of a letter like this:
> Name
> Address 1
> Address 2
> CityStateZip
> I want to put them all in one textbox but sometimes Address 2 is NULL and
> I
> would like to supress the one line. The code looks like this:
> =Fields!FullName.Value & vbcrlf & Fields!Line1.Value & vbcrlf &
> Fields!Line2.Value & vbcrlf & Fields!CityStateZip.Value
> Does anybody know how to supress the NULL line? Is there a better way to
> do
> this?
> --
> Zip|||We use a lot of these too. I package the second Address field in an 'Iif'
statement like so, where if the Address2 field is NULL an empty string is
simply appended onto Address1, otherwise there is a line break and then the
Address2 Field:
Fields!Address1.Value &
Iif((IsNothing(Fields!Address2.Value, "", vbcrlf & Fields!Address2.Value)
& vbcrlf & Fields!CityStateZip.Value
There may be cleverer ways to do it but that works for me.
Hope that helps.
rgorslin
"Zip" wrote:
> I need a little help in formatting the address field. We use a 3 or 4 line
> address field at the top of a letter like this:
> Name
> Address 1
> Address 2
> CityStateZip
> I want to put them all in one textbox but sometimes Address 2 is NULL and I
> would like to supress the one line. The code looks like this:
> =Fields!FullName.Value & vbcrlf & Fields!Line1.Value & vbcrlf &
> Fields!Line2.Value & vbcrlf & Fields!CityStateZip.Value
> Does anybody know how to supress the NULL line? Is there a better way to do
> this?
> --
> Zip

Format Address as EMail Link

How can I format a column so that the email address is clickable and will
start up a new mail message?On Jun 8, 10:26 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> How can I format a column so that the email address is clickable and will
> start up a new mail message?
Normally, you can use "mailto:someperson@.someURL.com." I'm not sure if
you can use it as part of an 'a' tag w/html or not. In that case it
would be:
<a href="http://links.10026.com/?link=mailto:someperson@.someURL.com">SomeDescription</a>
Of course, you can append different text/fields as a report
expression, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||>>I'm not sure if you can use it as part of an 'a' tag w/html or not.
The answer is yes, you can.
>L<
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1181327056.703585.10240@.h2g2000hsg.googlegroups.com...
> On Jun 8, 10:26 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
>> How can I format a column so that the email address is clickable and will
>> start up a new mail message?
>
> Normally, you can use "mailto:someperson@.someURL.com." I'm not sure if
> you can use it as part of an 'a' tag w/html or not. In that case it
> would be:
> <a href="http://links.10026.com/?link=mailto:someperson@.someURL.com">SomeDescription</a>
> Of course, you can append different text/fields as a report
> expression, etc. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Thanks, both of you got me pointed in the right direction.
I accomplished this by:
Right click on the text box containing an email address
Click Navigation
Enter ="mailto:"+Fields!Email.Value in the Jump to URL
Formatted text box with an Underline
"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
news:uzWENRgqHHA.1168@.TK2MSFTNGP03.phx.gbl...
>>I'm not sure if you can use it as part of an 'a' tag w/html or not.
> The answer is yes, you can.
>>L<
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1181327056.703585.10240@.h2g2000hsg.googlegroups.com...
>> On Jun 8, 10:26 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
>> How can I format a column so that the email address is clickable and
>> will
>> start up a new mail message?
>>
>> Normally, you can use "mailto:someperson@.someURL.com." I'm not sure if
>> you can use it as part of an 'a' tag w/html or not. In that case it
>> would be:
>> <a href="http://links.10026.com/?link=mailto:someperson@.someURL.com">SomeDescription</a>
>> Of course, you can append different text/fields as a report
>> expression, etc. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||On Jun 8, 2:54 pm, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> Thanks, both of you got me pointed in the right direction.
> I accomplished this by:
> Right click on the text box containing an email address
> Click Navigation
> Enter ="mailto:"+Fields!Email.Value in the Jump to URL
> Formatted text box with an Underline
> "Lisa Slater Nicholls" <l...@.spacefold.com> wrote in messagenews:uzWENRgqHHA.1168@.TK2MSFTNGP03.phx.gbl...
> >>I'm not sure if you can use it as part of an 'a' tag w/html or not.
> > The answer is yes, you can.
> >>L<
> > "EMartinez" <emartinez...@.gmail.com> wrote in message
> >news:1181327056.703585.10240@.h2g2000hsg.googlegroups.com...
> >> On Jun 8, 10:26 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> >> How can I format a column so that the email address is clickable and
> >> will
> >> start up a new mail message?
> >> Normally, you can use "mailto:someper...@.someURL.com." I'm not sure if
> >> you can use it as part of an 'a' tag w/html or not. In that case it
> >> would be:
> >> <a href="http://links.10026.com/?link=mailto:someper...@.someURL.com">SomeDescription</a>
> >> Of course, you can append different text/fields as a report
> >> expression, etc. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant
Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultant