Query Concatenation

[Rjc8513]

Can someone help me concatenate this one?

I want to combine the fields:
[LAST NAME] & "," & [FIRST NAME]

in a SELECT statement as the field "EMPL NAME".

Can't seem to get it to work. Thanks.

 

[cmmrfrds]

Try something like. Although, I don't believe in using spaces in data names.

([LAST NAME] & "[,]" & [FIRST NAME]) as [EMPL NAME]

 

[jebry]

Hi!

If you are in the query design screen then you use:

[Empl Name]: [Last Name] & ", " & [First Name]

hth
Jeff Bridgham
bridgham@purdue.edu

 

[compucop]

If you trying to query a list of names from the employee table, try this.

dim rs as adodb.recordset
dim cn as adodb.connection
set rs = new adodb.recordset
set cn = currentproject.connection

RS.Open "SELECT * FROM EMPLNAME WHERE LASTNAME LIKE '" & TEXT.Value & "%' AND FIRSTNAME LIKE '%" & Text.Value & "%' ORDER BY LNAME,FNAME", CN, adOpenStatic

Hope this helps.

 

[Rjc8513]

The full code is:

dbs.CreateQueryDef "sqry- MPOO EMF QBF by mpoo 2", _
"SELECT [tbl EMF ARCHIVE].[FINANCE NO], [tbl EMF ARCHIVE].[POST OFFICE], " & _
"[tbl EMF ARCHIVE].[YRPP],[tbl EMF ARCHIVE].[WEEK], " & _
"[tbl EMF ARCHIVE].[PAY LOC], [tbl EMF ARCHIVE].[DES ACT], " & _
"[tbl EMF ARCHIVE].[LDC], [tbl EMF ARCHIVE].[SSN], " & _
"[tbl EMF ARCHIVE].[LAST NAME]'" & "', '" & "'[tbl EMF ARCHIVE].[FIRST NAME] AS [EMPL NAME], " & _
"[tbl EMF ARCHIVE].[WKHRS], [tbl EMF ARCHIVE].[OT], " & _
"[tbl EMF ARCHIVE].[POT], [tbl EMF ARCHIVE].[TOTAL], " & _
"[tbl EMF ARCHIVE].[AL], [tbl EMF ARCHIVE].[SL], " & _
"[tbl EMF ARCHIVE].[LWOP], [tbl OFFICE LISTING].[MPOO] " & _

"FROM [tbl EMF ARCHIVE] INNER JOIN [tbl OFFICE LISTING] ON " & _
"[tbl EMF ARCHIVE].[FINANCE NO]=[tbl OFFICE LISTING].[FINANCE NO] " & _

"GROUP BY [tbl EMF ARCHIVE].[FINANCE NO], [tbl EMF ARCHIVE].[POST OFFICE], " & _
"[tbl EMF ARCHIVE].[YRPP],[tbl EMF ARCHIVE].[WEEK], " & _
"[tbl EMF ARCHIVE].[PAY LOC], [tbl EMF ARCHIVE].[DES ACT], " & _
"[tbl EMF ARCHIVE].[LDC], [tbl EMF ARCHIVE].[SSN], " & _
"[tbl EMF ARCHIVE].[LAST NAME],[tbl EMF ARCHIVE].[FIRST NAME], " & _
"[tbl EMF ARCHIVE].[WKHRS], [tbl EMF ARCHIVE].[OT], " & _
"[tbl EMF ARCHIVE].[POT], [tbl EMF ARCHIVE].[TOTAL], " & _
"[tbl EMF ARCHIVE].[AL], [tbl EMF ARCHIVE].[SL], " & _
"[tbl EMF ARCHIVE].[LWOP], [tbl OFFICE LISTING].[MPOO] " & _

"HAVING ((([tbl OFFICE LISTING].[MPOO])=Left([Forms].[frm MPOO EMF QBF].[cbxSEARCH],1))) " & _

"ORDER BY [tbl EMF ARCHIVE].[FINANCE NO], [tbl EMF ARCHIVE].[YRPP], " & _
"[tbl EMF ARCHIVE].[WEEK], [tbl EMF ARCHIVE].[PAY LOC], " & _
"[tbl EMF ARCHIVE].[DES ACT],[tbl EMF ARCHIVE].[LDC],[tbl EMF ARCHIVE].[SSN];"