CODEWITHSUNDEEP
sqlsql-serverms-access
Stuey1964
Stuey1964

Reputation: 15

Access field contents length 255 characters

I have an SQL table linked in MS Access which contains a number of short text fields that are limited to 255 characters. This table is updated from an Access form.

I was informed that when data is extracted from the table one of the fields is excessively long based on the contents.

I investigated and ran this query:

SELECT [dbo_NCR User Input].ImpactGrade, Len([impactgrade]) AS length
FROM [dbo_NCR User Input];

...which showed that regardless of the contents, the field length was 255 characters:

Query Results

Has anyone experienced this issue and if so how can it be resolved to remove the additional characters from the field?

Upvotes: 1

Views: 923

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

If the data type of the field in the SQL Server table is CHAR(255) as opposed to VARCHAR(255), then 255 bytes are always allocated for the field value, regardless of the true length of the content.

Conversely, VARCHAR(255) only allocates the bytes required to store the content of the field (+2 bytes), up to the given maximum.

Upvotes: 1

Related Questions