CODEWITHSUNDEEP
ms-accesscreate-table
user3376020
user3376020

Reputation:

Prevent extra space being inserted into a CHAR field?

I create a table in MS Access using the following script:

CREATE TABLE POWERSQL (
 ProposalNumber  INTEGER  PRIMARY KEY,
 FirstName   CHAR (15),
 LastName   CHAR (20),
 Address    CHAR (30),
 City    CHAR (25),
 StateProvince  CHAR (2),
 PostalCode   CHAR (10),
 Country    CHAR (30),
 Phone    CHAR (14),
 HowKnown   CHAR (30),
Proposal   CHAR (50),
BusinessOrCharity CHAR (1) );

When insert some value to a CHAR filed which is shorter than the set length, e.g., 'John' in [FirstName], it fills the remaining characters with empty space which messes up with other queries and joins. Trim does not help. Any advice? Many thanks.

Upvotes: 0

Views: 537

Answers (2)

forpas
forpas

Reputation: 164099

Instead of the fixed length CHAR(x) data type, use TEXT, like:

FirstName   TEXT,
.................

The TEXT data type is variable length and can store up to 255 characters

Upvotes: 0

Erik A
Erik A

Reputation: 32642

CHAR fields are half-supported in MS Access and are fixed-length. This means you can't insert less characters than the full field length, and if you do, Access will fill the unused positions with spaces.

Instead, use VARCHAR for variable-length character fields:

CREATE TABLE POWERSQL (
 ProposalNumber  INTEGER  PRIMARY KEY,
 FirstName   VARCHAR (15),
 etc...

Upvotes: 3

Related Questions