Unwanted Trailing Spaces in Form Fields

[Webcodger]

I have a table consisting of several string fields. I created a form to display the data in the table. When the data is displayed, each control automatically fills the field with the data plus any trailing blanks necessary to make up the entire alloted space in the field. This is not wanted because, to add text to the control/field, you must first delete the necessary number of trailing blanks.
Does anyone know how to prevent the extra blanks? My OS is Vista Home Premium and I'm using Office XP.

 

[jdraw]

I have a table consisting of several string fields. I created a form to display the data in the table. When the data is displayed, each control automatically fills the field with the data plus any trailing blanks necessary to make up the entire alloted space in the field. This is not wanted because, to add text to the control/field, you must first delete the necessary number of trailing blanks.
Does anyone know how to prevent the extra blanks? My OS is Vista Home Premium and I'm using Office XP.

Use a Trim function on your field.
Trim will remove leading and trailing spaces. ie Trim(MyFieldWithSpaces)
RTrim will remove trailing spaces,
LTrim will remove leading spaces.

 

[Webcodger]

Thanks for the reply, jdraw. I tried that...seven ways to Sunday. I tried to put a trim on the field name in the form's control, trying to get the data to default to the trimmed format. I tried using a query in stead of the table, hoping the query would be more forgiving.
I'm familiar with the trim functions. Problem is, I don't know how to apply it to a control on a form that already has data displayed.

 

[vbaInet]

There might be a Format or Input Mask set. Check the controls on your form and your fields in the related tables for those properties.

 

[pbaldy]

Also, what is the data in? It sounds like a Char data type in SQL Server.

 

[Webcodger]

vbaInet, the trailing nuisance only manifests itself in the form, not the table.
Paul, If the table that is being populated from the form was created with an SQL statement such as DB.Execute "CREATE TABLE tblSomething (vField char(33)...etc.
would that qualify for a "data type in SQL server"? 'Cause that is how I created the table.
But that's not it. I created another table from the Access wizard without SQL and it does the same thing.
I'm just about ready to throw in the towel on this one, at least for a while.
Thanks for all help.

 

[pbaldy]

If it's only in the form and not the table, then it's not what I asked about. I can't think offhand of any property that would do this. Did you check the properties vbaInet mentioned (the properties of the control on the form, not the field in the table)? Can you post the db?

 

[ajetrumpet]

to add text to the control/field, you must first delete the necessary number of trailing blanks..

this is not normal. in other words, you're saying that if the field length is set to 250, and the cell says, "test", changing the record in a form will require 246 backspace key strokes before you get to the actual value in the cell! right?

have you tried making a new table or a new form? i doubt this has anything to do with a mask.

 

[vbaInet]

You could also try checking the Len of both the Field's value and that of the control's value using Debug.Print or a message box. There might be some underlying code that is padding this up too.

ajetrumpet has a good suggestion for you too.

 

[missinglinq]

I think you need to tell us what you're actually doing here!

DB.Execute "CREATE TABLE tblSomething (vField char(33))

is not a VBA/Access Sql statement. What are you using for your back end? As Paul said, there's nothing inherent in Access that would do this. There are no Fixed-Length strings in Access, and textboxes don't have any set lengths at all. They can hold a basically unlimited number of characters.

Just to check for corruption, I would create a blank database and import all objects into it, and see if the problem persists.

 

[Webcodger]

I think I have found the source of the trailing blanks, and, perhaps Paul and others may have touched on it, but I was not schooled enough to pick up on the hint. If I create my table in design view (or perhaps with the Wizard), the trailing blanks don't show up. If I create the table using SQL, they do. My tables do not reside on an SQL server, however. They are in a separate access database.
I haven't had a chance to test this finding out thoroughly, but it appears to be the solution. I'll need some time to prove it out.
Thanks for all input, Guys. As a newcomer to online communication, I learned a bit about how to state the problem. Appreciate the help.

 

[GIBO69]

Set filed vith "TEXT (xxx)" instead "CHAR (xxx)" where xxx is the lengh of field.
In this way field will not be padded with spaces