Concatenate


G

Guest

I've tried reading the help and some of these questions but I still dont get
it. Everybody talks about using the & operator but where and how???

I'm looking to concatenate Forename and Surname so when a person is selected
from a lookup field both names remain in the box not just the forename. I
think this is the correct expression I just dont know where to stick it:

[Forename]&", "&[Surname]

Ian
 
Ad

Advertisements

G

Guest

[Forename]&", "&[Surname]

That is correct.
Make a select query, add the table that has the two fields and place this
expression in the field box. It will create a value like this: Expr1:
[Forename]&", "&[Surname]
Run the query.

this should do it.

GoodLuck!
 
G

Graham R Seach

That depends on where you are at the time.

If you're doing this from a stored query, where Forename and Surname are
fields in the table, then:
SELECT [Forename] & " " & [Surname] As [Name] FROM tblMyTable

If you're doing this from a stored query, where Forename and Surname are in
TextBoxes, then:
SELECT Forms!frmMyForm!txtForename AND Forms!frmMyForm!txtSurname As
[Name] FROM tblMyTable

If you're doing this in SQL from VBA, where Forename and Surname are fields
in the table, then:
sSQL = "SELECT [Forename] & ' ' & [Surname] As [Name] FROM tblMyTable"

If you're doing this in SQL from VBA, where Forename and Surname are in
TextBoxes, then:
sSQL = "SELECT [" & Me!txtForename & "] & [" & Me!txtSurname & "] As
[Name] FROM tblMyTable"

If you're doing this from VBA, where Forename and Surname are in variables,
then:
MsgBox Forename & " " & Surname

If you're doing this from VBA, where Forename and Surname are in TextBoxes,
then:
MsgBox Me!txtForename & " " & Me!txtSurname

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
G

Guest

There is no single answer to "where and how". If you are combining first and
last names for use in a combo box or list box you would probably concatenate
in the row source query by adding a calculated field:
FullName: [Surname] & ", " & [Forename]
In a text box on a form or report the control source in an unbound text box
would be:
= [Surname] & ", " & [Forename]
In VBA it depends on the situation. For instance:
MsgBox "A new record is being created for [Forename] & " " & [Surname]
or to populate an unbound text box:
Me.txtFullName = [Forename] & " " & [Surname]

If you need more assistance you will need to provide details of what you are
trying to do.
 
Ad

Advertisements

M

Marshall Barton

NoviceIan said:
I've tried reading the help and some of these questions but I still dont get
it. Everybody talks about using the & operator but where and how???

I'm looking to concatenate Forename and Surname so when a person is selected
from a lookup field both names remain in the box not just the forename. I
think this is the correct expression I just dont know where to stick it:

[Forename]&", "&[Surname]


Put it in a calculated field in the combo box's RowSource
query. E.g.

SELECT personID, Forename & ", " & Surname As FullName FROM
lookuptable ORDER BY Forename & ", " & Surname
 

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

Similar Threads


Top