Records contain extra spaces on import from SQL Server

[Guest]

Hi.

I'm creating a database project using SQL server to host the data tables
with a number of Access (2000 file format, but building in 2003) applications
for user input, reporting, etc. I've found that in all my forms the data is
returned from the server with trailing blank spaces up to the full field
size, if the record entry is less than the maximum length. So for example,
in a Notes field, the data table on the server is set to 'char' with a max
length of 250. On the linked table in the application this translates to
'text' with length 250. On the form, the control is filled in with less
text, but looks fine. Once the record is saved, on the next read back from
the Server, it will have had spaces added to make it up to 250. This causes
the last word to wrap onto the next line. The other problem is that on
trying to concatenate strings (so for example add a forename, a single space
and a surname) you end up with all the additional white space added in too.
I can use Trim in queries, etc to clean up the string, but it doesn't make
life easy when coding, displaying results, etc. For example, I want to right
justify text on a form, but the extra space pushes it back left. I can't
easily Trim it, or else I have to seperate the Form control from its Table
control and so cannot update the entry correctly.

Is there any way of preventing this behaviour?

Many thanks,

Steve C

 

[Jeff Boyce]

Steve

The "char" datatype pads with blanks. The "varchar" uses only as many
characters as are there. If you have any control over the SQL-Server side,
and if it doesn't mess with others' use of the data, fix that data type.

If you have no control there, or other users insist on the padding, you can
use an update query and the Trim() function to replace the padded fields
with their "trimmed" values.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Superb! Thanks Jeff.

This is a new project and my first foray into a SQL back-end. At the moment
it's all on my development server so I have complete con trol! )

I've done a lot of previous dev in pure access, so I guess there will be a
few gotchas as I learn the differences with SQL.

Many thanks.

Steve C.