September 28, 2009 at 10:07 am
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
September 28, 2009 at 10:29 am
Nic-1052152 (9/28/2009)
Hi allI 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
September 28, 2009 at 10:34 am
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
September 29, 2009 at 7:28 am
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