Combining fields

  • Thread starter Thread starter agogel
  • Start date Start date
A

agogel

I am concatenating 2 fields. I am not using the Trim
function. I am losing the trailing spaces on my first
field. How can I combine 2 fields, & keep any trailing
spaces from the first field?
 
add the spaces back in during the concatenation process.
example - "field1" & " " & "field2"
the spaces in the middle should show up in the
concatenated field.
 
Thanks for the reply, but it's not that easy. "field1"
doesn't always have trailing spaces. I don't want to add
the extra space if "field1" doesn't have a trailing space.
 
Normally, Access fields DO NOT have trailing spaces if they are stored in a
"standard" Access (Jet) table. So, why do you think your fields have trailing
spaces? Are they stored in an MS SQL Database with fixed length fields? Or in a
linked Excel worksheet or linked text file?

I believe you can get trailing spaces into an Access text field if you put them
there using a vba assignment; but, I would have to check that to make sure. If
you are just entering data into a table or a control on a form, then you almost
certainly don't have any trailing spaces, because Access strips them out automatically.
 
John Spencer (MVP) said:
Normally, Access fields DO NOT have trailing
spaces if they are stored in a
"standard" Access (Jet) table.

I believe you can get trailing spaces into an
Access text field if you put them
there using a vba assignment; but, I would have
to check that to make sure.
Click to expand...

Here's a quick test that shows a text column/field in a Jet table may
indeed store trailing spaces:

CREATE TABLE NewTestTable
(
MyCol1 VARCHAR(255)
)
;

INSERT INTO NewTestTable
(MyCol1) VALUES ('test ')
;

SELECT
LEN(MyCol1)
FROM
NewTestTable
;

Jamie.

--
 
And as I said you can do it using VBA. You can't do it inputting via the
keyboard into the table or into a query or into a control on a form.

Or maybe that is what you are saying and you are just confirming what I said.
 
...
And as I said you can do it using VBA. You can't do it inputting via the
keyboard into the table or into a query or into a control on a form.

Or maybe that is what you are saying and you are just confirming what I said.
Click to expand...

I used a standard Jet table and a keyboard but not MS Access. Perhaps
you could claify what you meant by, "Normally, Access fields DO NOT
have trailing spaces if they are stored in a 'standard' Access (Jet)
table."

Jamie.

--
 
OK, open a table using ACCESS and in a text field type
" This is a test. "
Move out of the field.
Move back into the field at the very end.
Where is the cursor? I think you will find it is at the period.

Your example didn't use the keyboard to input the data into a field in a table
(or a control on an ACCESS form) it used an SQL statement to populate the
field without the interface of an ACCESS table, query or form.

In other words, JET (the native db for Access) can store trailing spaces. In
most cases the Access input is done via a keyboard into a form, query
datasheet view, or table datasheet view. Access seems to automatically strip
any trailing spaces.

So if you are using the JET engine, but not ACCESS, then you can have trailing
spaces stored in the table.

At this point, I think we need to get back to the original question and
original poster - unless you are the original poster. I don't think you are,
since the knowledge you have displayed to this point indicates to me that you
would have no problem solving the original question.
 
spencer4 said:
At this point, I think we need to get back to the original question
Click to expand...

There was a question? <g>

If you hadn't mentioned Jet, I'd assume that you were using 'Access
table' and 'Access field' as euphemisms for 'Jet table' and 'Jet
column'. But mention Jet and SQL Server in the same post and things
start sounding a bit confused e.g. 'Access table' is a Jet table in
which settings required by the MS Access UI are stored?

Jet tables hold the application data. My test, using SQL, demonstrates
that Jet does not remove any trailing spaces. We are both in agreement
that it seems to be the MS Access UI that removes trailing spaces.

Jamie.

--
 
Back
Top