Display values from 1st and 2nd field

L

LaDdIe

Hi,
I have a simple table that has two columns surname and firstname,
how do I combine the results into a third column?

Good at Excel but very new to Access.

Thanks.
 
K

Ken Sheridan

Do it in a query like so:

SELECT (FirstName + " ") & Surname AS FullName
FROM YourTable;

In query design view you'd do it by entering the following in the 'field'
row of a blank column in query design view:

FullName: ([FirstName + " ") & [Surname]

By using the + operator to add the space between the names the space will be
suppressed if by any chance someone was recorded only by their surname. This
is because Null + " " = Null (the Null propagates), whereas Null & " " = " "
so would insert an unwanted leading space.

Do not under any circumstances store the full name as a separate column in
the table, always return it in a query as described above, or in an unbound
control on a form or report using a similar expression as the ControlSource
property. Its much easier to build values from several columns than to parse
a value in a single column into separate values.

BTW if you don't already have one, add a numeric ID column of unique values
(e.g. an autonumber) as the table's primary key. Names are not suitable for
keys as they can be duplicated.

Ken Sheridan
Stafford, England
 
K

Ken Snell \(MVP\)

Write a query to show this, using a calculated field to show the
concatenated result:

SELECT FirstName & " " & SurName AS FullName
FROM TableName;
 
R

Rick Brandt

LaDdIe said:
Hi,
I have a simple table that has two columns surname and firstname,
how do I combine the results into a third column?

Good at Excel but very new to Access.

In a query add a new column to the designer...
FullName: firstname & " " & surname

In a TextBox ControlSource on a form or report...
= firstname & " " & surname

In a table (you don't).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top