Concatenation in an SQL Statement

Hi,

does anyone know how to concatenate two fields in an SQL statement in Access.

in standard SQL this would look like:

select * FROM tbCalls WHERE 'Fred Bloggs' = tbCalls.Firstname || tbCalls.Surname;

Access doesn't like the || in the statement, is there a replacement for that would work.

Thanks Jon
jonkyAsked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
You can't use the field alias in the WHERE clause, so you'll need to use something like:

SELECT * FROM tbCalls WHERE Firstname & " " & Surname = "Fred Bloggs"
0
 
hessmacCommented:
you can concatenate alphanumeric and numeric values with the & operator.

ps: your statement will not work.
    check the where clause from your select statement !!




0
 
bistricaCommented:
select *, (tbCalls.Firstname & " " & tbCalls.Surname) AS full_name FROM tbCalls WHERE full_name = 'Fred Bloggs';
0
 
hessmacCommented:
Copy & Past into the msaccess sql-sheet of a new query.
Change the Table/Fieldnames to your Table/Fieldnames.  
Option 1:

SELECT [Table3].[Firstname] & " " & [Table3].[Lastname] AS Expr1
FROM Table3
WHERE ((([Table3].[Firstname] & " " & [Table3].[Lastname])="Fred Bloggs"));


Option 2 with the "LIKE"-option:

SELECT [Table3].[Firstname] & " " & [Table3].[Lastname] AS Expr1
FROM Table3
WHERE ((([Table3].[Firstname] & " " & [Table3].[Lastname]) Like "fREd bloGgs*"));


Option 3 is the same as option 2 except for the extra field we display:

SELECT [Table3].[Firstname] & " " & [Table3].[Lastname] AS Name, [Table3].[ID]
FROM Table3
WHERE ((([Table3].[Firstname] & " " & [Table3].[Lastname]) Like "fREd bloGgs*"));

0
 
1WilliamCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to shanesuebsahakarn
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
1William
EE Cleanup Volunteer
0
All Courses

From novice to tech pro — start learning today.