Re: MS Query Problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.
--
Dave Hargis, Microsoft Access MVP


"Me!" wrote:

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" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:92F7CD87-063A-4FD2-9EA0-E64DC5A1A68F@xxxxxxxxxxxxxxxx
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.
--
Dave Hargis, Microsoft Access MVP


"Me!" wrote:

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






.

Relevant Pages

  • Re: Conversion error
    ... text (character strings) before importing them into SQL ... I have defined all columns in the SQL Server ... all values properly to varchar. ... money data. ...
    (microsoft.public.sqlserver.dts)
  • Re: BULK COPY changes row order importing Text file
    ... Error = [ODBC SQL Server Driver]Invalid character value for cast ... BCP copy in failed ...
    (microsoft.public.sqlserver.programming)
  • RE: strange problem with sql
    ... The difference in the character _ and W is just one bit. ... There are other letters that are getting mistranslated: ... My onlly guess now is that there is a memory ... > that at one occassion the SQL Server executed was changed on one ...
    (microsoft.public.sqlserver.programming)
  • RE: strange problem with sql
    ... The difference in the character _ and W is just one bit. ... There are other letters that are getting mistranslated: ... My onlly guess now is that there is a memory ... > that at one occassion the SQL Server executed was changed on one ...
    (microsoft.public.data.ado)