Problems mergeing two text fields

[lexxor812 via AccessMonster.com]

Hi,

I have two text fields, [firstname] and [lastname]. Both fields are set to
length 20. I am trying to merge the two fields into one field in a query
using fullname: [firstname] &" "& [lastname] , and then use the fullname
field to merge on a form letter in Word.

My problem occurs that the Query& Word document come out looking like:
firstname lasname

I think the problem is that the firstname field is set to 20 length, but I do
not know how to have access truncate this in the query/ or table. This seems
to be the case with the 5 results I checked , the total of the firstname and
space until the lastname is 20.

I am hoping I can fix this on the table side of it, as I have multiple
queries and merge letters based on the one table.

 

[Jeanette Cunningham]

Hi lexxor812
In the query design view, remove the column for firstname and the column for
lastname (to make it easier to see only the fullname).
Now switch the query to datasheet view and you should be able to see
fullname.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[lexxor812 via AccessMonster.com]

I am not sure if you are understanding the problem:

I have a table "name" with 2 fields, [firstname] and [lastname]
So, one of my records would be [John] and [Smith] (as firstname and lastname)

I have a query with one field = fullname: [name]![firstname] &" "& [name]!
[lastname]

My result of the query appears as: John Smith

There is a space between John and Smith, I am not understanding how to fix, I
am thinking it is due to [firstname] field set to length 20.....

Hi lexxor812
In the query design view, remove the column for firstname and the column for
lastname (to make it easier to see only the fullname).
Now switch the query to datasheet view and you should be able to see
fullname.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

[quoted text clipped - 16 lines]

I am hoping I can fix this on the table side of it, as I have multiple
queries and merge letters based on the one table.

 

[Bob Barrows]

Use the RTrim function:
fullname:RTrim([firstname]) &" "& [name]! [lastname]

The length setting should not be the problem because Access does not pad
data with spaces. I suspect that the data entry process is adding the
spaces.

I am not sure if you are understanding the problem:

I have a table "name" with 2 fields, [firstname] and [lastname]
So, one of my records would be [John] and [Smith] (as firstname and
lastname)

I have a query with one field = fullname: [name]![firstname] &" "&
[name]! [lastname]

My result of the query appears as: John Smith

There is a space between John and Smith, I am not understanding how
to fix, I am thinking it is due to [firstname] field set to length
20.....

Hi lexxor812
In the query design view, remove the column for firstname and the
column for lastname (to make it easier to see only the fullname).
Now switch the query to datasheet view and you should be able to see
fullname.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

[quoted text clipped - 16 lines]

I am hoping I can fix this on the table side of it, as I have
multiple queries and merge letters based on the one table.

 

[fredg]

I am not sure if you are understanding the problem:

I have a table "name" with 2 fields, [firstname] and [lastname]
So, one of my records would be [John] and [Smith] (as firstname and lastname)

I have a query with one field = fullname: [name]![firstname] &" "& [name]!
[lastname]

My result of the query appears as: John Smith

There is a space between John and Smith, I am not understanding how to fix, I
am thinking it is due to [firstname] field set to length 20.....

Hi lexxor812
In the query design view, remove the column for firstname and the column for
lastname (to make it easier to see only the fullname).
Now switch the query to datasheet view and you should be able to see
fullname.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

[quoted text clipped - 16 lines]

I am hoping I can fix this on the table side of it, as I have multiple
queries and merge letters based on the one table.

Actually, the fact that you have set the Field length to 20 is
irrelevant.
Data is NOT padded just because you set the length to 20. It still
takes up (for "John") just 4 characters, and (for "Smith") just 5. You
just cannot enter data of more than 20 characters. Something else is
going on. Perhaps the difficulty is you somehow are leading the last
name with spaces " Smith", or you have imported the data that has
a fixed length.

In any event, in your query, try:
FullName:Trim([FirstName] & " " & Trim([LastName])
Does that help?

 

[Jeanette Cunningham]

When you look at the data in the table, are there any other characters or
spaces before the Smith or any other last names in the Last name field.

Create a query based on the table with the names. Drag the last name field
on to the query grid.
In the next column create a calculated field by typing L: Len([lastname]) in
the field row.

Switch the query to datasheet view and see if the value for len is the same
as the number of characters in each last name. If the L column has a value
bigger than the number of characters in each last name, that is where the
extra spaces are coming from.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I am not sure if you are understanding the problem:

I have a table "name" with 2 fields, [firstname] and [lastname]
So, one of my records would be [John] and [Smith] (as firstname and
lastname)

I have a query with one field = fullname: [name]![firstname] &" "& [name]!
[lastname]

My result of the query appears as: John Smith

There is a space between John and Smith, I am not understanding how to
fix, I
am thinking it is due to [firstname] field set to length 20.....

Hi lexxor812
In the query design view, remove the column for firstname and the column
for
lastname (to make it easier to see only the fullname).
Now switch the query to datasheet view and you should be able to see
fullname.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

[quoted text clipped - 16 lines]

I am hoping I can fix this on the table side of it, as I have multiple
queries and merge letters based on the one table.