How do I concatenate fields when I'm writing code?

S

Stephen Glynn

I'm trying to adapt some code I've found at
http://www.databasedev.co.uk/filter_combo_boxes.html
to filter a combo box selection on the basis of another combo box
selection.

I've got it working but I'd like to concatenate [Contacts].[FirstName]
and [Contacts.LastName]. I know how to do it in an ordinary query, but
how do I do it when I'm writing the SQL in code? FullName AS
[Contacts].[FirstName]&" "&[Contacts.LastName] doesn't work, I suspect
because I should somehow escape the '&" "&'.

Can anyone advise, please?

Steve
 
A

Al Camp

Stephen,
Create your query using the query design grid. When the query works just
the way you want, select View SQL. This will show younthe SQL equivalent of
the query you built with the grid.
Just Cut and Paste that SQL statement into your VB code, where needed.
 
S

Steve Schapel

Steve,

First of all, it's back to front. Secondly, you have got it all
scrunched up with no spaces around the &s. And thirdly, if you are
using a SQL string in VBA code, you will need to replace the "s with 's
otherwise it will get mistaken for the end of the string. If I
understand you correctly, try like this...
... [Contacts].[FirstName] & ' ' & [Contacts.LastName] AS FullName
 
J

John Vinson

... [Contacts].[FirstName] & ' ' & [Contacts.LastName] AS FullName
Click to expand...

just another possible error in the original - should be

[Contacts].[LastName]

Contacts is one object (a table), LastName is another object (a field)
- each should have its own brackets.

John W. Vinson[MVP]
 
S

Steve Schapel

Good catch. Thanks, John!

--
Steve Schapel, Microsoft Access MVP


John said:
... [Contacts].[FirstName] & ' ' & [Contacts.LastName] AS FullName
Click to expand...


just another possible error in the original - should be

[Contacts].[LastName]

Contacts is one object (a table), LastName is another object (a field)
- each should have its own brackets.

John W. Vinson[MVP]
Click to expand...
 
S

Stephen Glynn

Steve said:
Good catch. Thanks, John!
Click to expand...

Sorry. It was late at night and I was typing from memory.

Thanks for the help; before reading the replies, I'd found a work-round
-- I just concatenated my fields in a query with just two fields,
ContactsID and FullName, and pointed the code towards that. Cut down
the room for typos. I see I'd gone wrong with the ampersands and quote
marks in the code.

Steve
 
Top