field being padded with blanks...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following subroutime that writes a record to a table. When the
assignment statement - ![ConcWithDrug] = myConcDrug - is executed, the
field in the recordset is padded with blanks or unprintable characters. The
field that has the original value has no extra characters (I know this from
viewing the values in Debug mode).When I later read the value from the table
and include it in a string that gets printed in a messagebox, it prints those
extra characters, even if I do a Trim. Any ideas why this would happen?
Here's the subroutine. I've NEVER had this happen before and don't have a
clue!

Public Sub WriteConc(ByVal itemID As Long, ByVal myConcDrug As String, _
ByVal sameBag As Boolean)

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tblItemsConc", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rst
.AddNew
![ConcItemID] = itemID
![ConcWithDrug] = myConcDrug
If sameBag Then
![ConcType] = "S"
End If
.Update
.Close
End With

End Sub
 
If the field is being padded, then either we have a fixed width field, or a
fixed length string. Given the ByVal declaration, and the fact that Trim()
does not work, let's assume the field is fixed width.

To fix the problem, open the table in design view, and create a new field of
type Text and make the Field Size big enough to accept the data. Save. Close
the table. Create an Update query to populate this field with:
Trim([ConcWithDrug])
After verifying the field now has the right data, delete the old field, and
rename the new one as ConcWithDrug if you wish. (Before you do that, I
strongly suggest you turn off the Name AutoCorrect options under Tools |
Options | General.)

You cannot create a field of this type through the Access interface, but you
if the table is created programmatically or if it is an attached table in
another database. The most common way of doing this is by executing this
type of query:
CREATE TABLE Table1 (Field1 CHAR);
The results vary depending how to execute the query (DAO or ADO), but you
can get a fixed width field. The statement you probably need is:
CREATE TABLE Table1 (Field1 TEXT (50));

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

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

message
news:[email protected]...
 
Well, you're partially right. I didn't create the table origianlly through
code, and it seemed to be working fine originally - the field in question was
created through the Access panels. But then I added a new CHAR field to the
table programatically, and since then the problem seems to have crept up.
Very strange. I seem to remember having problems when I tried to create
fields with TEXT programatically, and switching to CHAR worked. But I'm
relatively new at using the CREATE/ALTER commands in code, so I'll give it
another shot. Thanks for the info.
 
Back
Top