Concatenate

[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

 

[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!

 

[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

 

[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.

 

[Marshall Barton]

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