Field formatting when quering SQL server via Access

[micang]

Access 2003 - SQL 2005

Hi All,

Not sure where to post this, apologies if incorrect.

I have a few tables linked from Access to SQL Server 2005.

Problem is, when data is retrieved from SQL to Access, the fields in Access are "padded", i.e. - if the field in the SQL table is a varchar (10), then when the data is in Access, the field length is 10, irrespective if there is 1 character or 10 in the original SQL field. E.G - Field value in Server is "Hello", when retrieved into Access, it is "Hello ".

Any info appreciated.

Michael

 

[PHV]

You may use thr Trim function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[micang]

Hi PHV,

Yep, that is what I have done, seems a bit time consuming having to trim all fields though?

I am thinking of using Access as the frontend to query the tables on the server, but maybe thisis not the ideal solution!

Thanks for your input, much appreciated.

Michael

 

[jsteph]

Micanguk,
If you use a pass-thru query, it will not be a big drag on the calculations, unless you're using one of the Trim'd fields as criteria. If that's the case, adjust your criteria (pad it accordingly) in the WHERE clause, don't show that field, and show the trim'd column, using an alias.

Also, I believe the current ODBC drivers will correctly translate Trim to ltrim(rtrim([field])), so you may not even need a pass-thru.
--Jim

 

[micang]

Jim, thank you for your input, I was not aware of pass through queries! Busy reading up onit and should helpme out a lot.

Many thanks!

Michael