TRIM function not working.

[jal]

Apparently there is a design issue which I don't understand. I have a table with an Account column (account number). It's of type TEXT and length 50.

For some reason Jet has it as a fixed-length column. How do I undo that? That is to say, even if I insert a 10-digit account number, Jet is storing a string of 50 characters (40 spaces).

I'm accessing the DB from VB.Net (actually C#.Net) but have the same problem even when I try it from Access VBA. Due to the fixed-width, even if I run this query,

UPDATE Posted SET Account = Trim(Account)

Jet ignores it - I still get a 50 char string when I run a select query from the table.

SELECT Account FROM Posted


The only way to get a 10-char string is this:

SELECT Trim(Account) FROM Posted


How did I get a fixed-width column?

 

[DJkarl]

What did your DDL statement look like to create the table? Did you use CHAR(50) or TEXT(50)? If you used CHAR(50) that told Jet to make it a fixed width field, if you use TEXT(50) that tells Access it is a variable length field.

 

[jal]

I don't recall using a DDL statement but it was a long time ago so perhaps I did.

Oddly the following didn't work (though it seemed to be what Allen Browne suggested)

Dim tDef As DAO.TableDef
Set tDef = CurrentDb.TableDefs("Posted")
Dim fld As DAO.Field
Set fld = tDef.Fields("Account")
fld.Attributes = fld.Attributes Xor dbfixedwidth

got runtime error 3420 "Object Invalid or No longer set"


I guess I'll try deleting the column and recreating it. But thanks for the info, I had no idea that Char(50) creates fixed width.