text field being padded with blank...

[Guest]

I have two fields defined in a table, each as a text field with a length of
5. They are defined identically. I create a new record in a recordset using
..AddNew and assign values to these fields., then use .Update to write the new
record. I assign values to each field using a String variable. When the
variable has a length of four, one of the fields works as expected, but the
other one ends up with a blank or unprintable character at the end. I went
into debug mode and viewed the values. At the assignment statement the string
variable has a value of "2809", but after it is assigned to the table's text
field, the text field has a value of "2809 ". I can't for the life of me
figure out why this is happening. Any idea? Any help is appreciated! Thanks.

Jim Burke

 

[Allen Browne]

Sounds like one of 2 things is happening. Either
- The string is a fixed length (so it has a trailing character), or
- The field is a fixed length, so it is always 5 characters long.

If it is a fixed-length string, it will be declared like this:
Dim MyString As String * 5
You can fix the problem by simply declaring:
Dim MyString As String

A fixed-width field cannot be created through the Access table design
interface. The most common way to accidently create such a field is by
executing a DDL query using CHAR(50) instead of TEXT(50) to create the
field.

The simplest workaround using the interface might be to:
1. Open the table in design view.
2. Rename the problem field. Save.
3. Create a new field of type Text. Save.
4. Use an Update query to populate the new field with:
Trim([MyOldField])
5. Delete the old field from the table after verifying the results.

An alternative approach using code (haven't tested this) might be:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(50);"
DBEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

You got it - I used DDL and CHAR(5). I was doing this way back when and
discovered the problem with using CHAR and changed my code that defines new
fields to use TEXT. But this field was defined before I made that change.
Thanks! That one was driving me nuts. I knew I had changed my definitions
from CHAR to TEXT but forgot that there were a couple of fields that had
already been created before that.

Sounds like one of 2 things is happening. Either
- The string is a fixed length (so it has a trailing character), or
- The field is a fixed length, so it is always 5 characters long.

If it is a fixed-length string, it will be declared like this:
Dim MyString As String * 5
You can fix the problem by simply declaring:
Dim MyString As String

A fixed-width field cannot be created through the Access table design
interface. The most common way to accidently create such a field is by
executing a DDL query using CHAR(50) instead of TEXT(50) to create the
field.

The simplest workaround using the interface might be to:
1. Open the table in design view.
2. Rename the problem field. Save.
3. Create a new field of type Text. Save.
4. Use an Update query to populate the new field with:
Trim([MyOldField])
5. Delete the old field from the table after verifying the results.

An alternative approach using code (haven't tested this) might be:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(50);"
DBEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I have two fields defined in a table, each as a text field with a length of
5. They are defined identically. I create a new record in a recordset
using
.AddNew and assign values to these fields., then use .Update to write the
new
record. I assign values to each field using a String variable. When the
variable has a length of four, one of the fields works as expected, but
the
other one ends up with a blank or unprintable character at the end. I went
into debug mode and viewed the values. At the assignment statement the
string
variable has a value of "2809", but after it is assigned to the table's
text
field, the text field has a value of "2809 ". I can't for the life of me
figure out why this is happening. Any idea? Any help is appreciated!
Thanks.

Jim Burke