Char(10) And Char(13)

  • Hi all

    I have two front ends for my database; an old one in access and a newer one in vb.net.

    I have a script which will script many new clients into the tables.

    The new clients come from a spreadsheet and have address split into four fields. Our database only has one field so I have concatenated them adding a new line between each. However i have come across a problem. Using Char(13) seems to cause issues with both front ends, meaning parts of Address2 end up on the first line when they should be on the 2nd etc etc. When i use Char(10) the vb.net front end seems to display the address fine but the Access one still has the same problem. please see my code below.

    ([Address 1] + Char(10) + IsNull([Address 2], ' ') + Char(10) +

    IsNull([Address 3], ' ') + Char(10) + IsNull([Address 4], ' '))

    The access front end displays the following as an example:

    1 High

    StreetSouthampton

    Hampshire

    whereas it should show

    1 High Street

    Southampton

    Hampshire

    When i do a select statement in ssms it seems fine, and when i use a letter writer which exports to word it seems fine.

    hope you can help.

    Nic

  • Nic-1052152 (9/28/2009)


    Hi all

    I have two front ends for my database; an old one in access and a newer one in vb.net.

    I have a script which will script many new clients into the tables.

    The new clients come from a spreadsheet and have address split into four fields. Our database only has one field so I have concatenated them adding a new line between each. However i have come across a problem. Using Char(13) seems to cause issues with both front ends, meaning parts of Address2 end up on the first line when they should be on the 2nd etc etc. When i use Char(10) the vb.net front end seems to display the address fine but the Access one still has the same problem. please see my code below.

    ([Address 1] + Char(10) + IsNull([Address 2], ' ') + Char(10) +

    IsNull([Address 3], ' ') + Char(10) + IsNull([Address 4], ' '))

    The access front end displays the following as an example:

    1 High

    StreetSouthampton

    Hampshire

    whereas it should show

    1 High Street

    Southampton

    Hampshire

    When i do a select statement in ssms it seems fine, and when i use a letter writer which exports to word it seems fine.

    hope you can help.

    Nic

    As you see access interpretes these chars in a different way the SSMS.

    Obviously ([Address 1] + Char(10) + IsNull([Address 2], ' ') + Char(10) + IsNull([Address 3], ' ') + Char(10) + IsNull([Address 4], ' '))

    Char(10) = newline

    You should provide all four individual columns data !

    We cannot interpret if a colum is empty, or if it is composed of two words !

    So provide the result for :

    Select [Address 1]

    , [Address 2]

    , [Address 3]

    , [Address 4]

    , ([Address 1] + Char(10) + IsNull([Address 2], ' ') + Char(10) +

    IsNull([Address 3], ' ') + Char(10) + IsNull([Address 4], ' ')) as contatenated

    from yourtable

    You can analyse it yourself by replacing the empty strings ('') by a known char.

    e.g.

    ([Address 1] + Char(10) + IsNull([Address 2], ' adr2 ') + Char(10) +

    IsNull([Address 3], ' adr3 ') + Char(10) + IsNull([Address 4], ' adr4 '))

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • The simple answer - SSMS is tolerant to line breaks in any style, unix, mac or windows. unix line breaks are Char(10), windows line breaks are char(13) + char(10). If you try replacing your char(10) with char(13) + char(10) you should find it will display correctly in both environments.

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • Thanks for the above, that seemed to solve it. 😀

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply