How do I format mailing address fields for display in reports

I am very new to development with SQL Server but I have lots of
experience with Access. I am producing a report in VB.Net using
CrystalReports that will display a company's address differently
depending on the contents of the data. For example, if the second
address line is NULL then I don't want it to display at all or if the
customer is not from the US then I want the country field displayed. My
database design is your typical address line 1, line 2, city, state,
postal code, country with lookup tables providing the full text for
state/provinces and countries. There's nothing fancy with the data.
My learning curve is both with Crystal and with SQL Server. I think
I'm better off trying to write a function in Transact SQL that I
could call from the query that VB.Net will use to create the XML that
will drive the report. I simply want to send a completely formatted
string to VB.Net, including commas, spaces and carriage returns and
line feeds. Please let me know if I'm nuts. I'm basing this
decision on the fact that Crystal is not the easiest tool to deal with
and that I probably have a better chance doing it on the server end.
I also have to believe that I'm not the first one who's ever wanted
to do this and am hoping that this code is out there somewhere for me
to legally pilfer and modify. I'm posting because my searches have so
far been unsuccessful.
Now, if anyone in Redmond is listening, those VB developers need to
walk down the hall and talk to the Access developers! I REALLY miss the
Access report writer and query builder. Their functionality and
productivity are superb. I find myself often linking Access to my SQL
Server databases, going into Access to build my query and then cutting
and pasting the SQL into Enterprise Manager. Now, if I could just write
my user-defined functions in VB or C then the world would then be a
much prettier and productive place.I kind of understand where you are coming from here, but you are probably
best served by doing this kind of formatting within crystal rather than in
T-SQL. This really is a presentation issue rather than a data issue.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>|||There is a *crystal* related microsoft newsgroup.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>