MS Query Problem

[Me!]

Hi,

I'm using MS Query to execute a stored procedure in an Access .adp sqlserver
link and return the data to Excel.

It works fine, but when the result set is returned to Excel, any fields with
trailing spaces have the trailing spaces truncated.

Does anyone know why this happens and if it can be avoided?

Many thanks......Jason

 

[Klatuu]

No, it can't be avoided directly. The database engine is dropping the
trailing spaces.

It would be a very unusual circumstance to keep trailing spaces. The only
solution would be to keep track of the desired length of the field and add
the spaces back to it, but I can't think of a reason to do so.

 

[Me!]

Hi Dave,

The field is a 20 character text string which represents an industry code,
with each character representing a particular 'aspect' of a car, i.e.
manufacturer, engine size, number of doors etc. and when the code was
originally designed a space was used as a null for each character field if
any aspect as null, rather than a non-standard character. And as it is an
industry standard code and embedded in 1000s of companies own systems a
retrogade changing of all codes to handle the spaces differently isn't
feasible.

So I'm left to try deal with it. SQL Server deals with it fine, it's just
access & excel that are a problem.

Cheers..........Jay

 

[Klatuu]

The reason SQL Server deals with it is because you can define fixed length
fields in SQL Server.

Now, what you can do is force it. That is, to add enough spaces at the end
of the string to make it 20 characters.

Lets say you enter this on a form in a control. You can use the after
update event of the text to force a length of 20:


Me.txtCode = Me.txtCode & Space(20 - len(Me.txtCode))

You will also want to use 20 spaces as the default value for the field so
that when a record is created, it will contain the spaces.