Go Back   Access World Forums > Microsoft Access Reference > Access FAQs

Closed Thread
Thread Tools Rate Thread Display Modes
Old 04-04-2010, 03:07 PM   #1
Join Date: Jun 2007
Location: Universe - Local Group - Milky Way Galaxy - Orion Arm
Posts: 5,640
Thanks: 0
Thanked 99 Times in 46 Posts
ajetrumpet has a spectacular aura about ajetrumpet has a spectacular aura about
Send a message via MSN to ajetrumpet Send a message via Yahoo to ajetrumpet
Horizontal Concatenation with Queries

I've never needed to do this, but I've seen questions on the forum that suggested that it is needed for some situations. Someone once asked me how to concatenate more than 20 fields into a single field without writing it out manually in the SQL statement. Well, here's how:

Here's the accompanying function that makes it work:
Option Compare Database

Public db As DAO.Database
Public rs As DAO.Recordset

Function concatfields(dataset As String, _
                      uniquefieldname As String, _
                      uniquefieldvalue As variant) As String

Dim strExpr As String
Dim strTemp As String
Dim i As Long

Set db = CurrentDb
Set rs = db.OpenRecordset(dataset, dbOpenDynaset)

strExpr = uniquefieldname & "=" & uniquefieldvalue

rs.FindFirst strExpr

For i = 0 To rs.Fields.Count - 1
    strTemp = strTemp & rs.Fields(i) & "DELIMITER HERE"
Next i


Set rs = Nothing
Set db = Nothing

strTemp = Left(strTemp, Len(strTemp) - 1)

concatfields = strTemp

End Function
*DATASET - Can be a table, query or SQL statement
*UNIQUE ID FIELDNAME - Name of a field that holds a unique identifier for each record
*UNIQUE ID FIELDVALUE - Name of that field in []. For example, a field named "ID" would be written like: [ID]
*TABLE(S) - the "FROM" clause of the statement that would result from the DATASET variable, regardless of what it is. SQL statements involving JOINS also work, but the tables must be listed to avoid throwing errors.

For complex queries, a little duplication is needed. Here's a query from one of my examples where fields are being pulled from 3 different INNER JOINED tables and then concatenated by a delimiter:
SELECT concatfields(

'Dataset is an actual SQL statement
"SELECT customers.customer, orders.orderdate, carriers.carrier, orders.orderid

FROM customers INNER JOIN (carriers INNER JOIN orders ON carriers.carrier = 

orders.carrier) ON customers.customer = orders.customer", "orderid", [orderid]) AS ConcatField

'FROM clause must be a duplicate of the dataset opened for JOINS
FROM customers INNER JOIN (carriers INNER JOIN orders ON carriers.carrier = 

orders.carrier) ON customers.customer = orders.customer

ajetrumpet is offline  
The Following User Says Thank You to ajetrumpet For This Useful Post:
hassanogaibi (06-28-2016)
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple queries changing criteria TClark14 Queries 9 03-16-2010 05:21 AM
Table entries from a drop down of all possible queries ejhatch Tables 2 02-16-2006 01:50 AM
Queries with an identity problem kupe Modules & VBA 9 10-28-2003 12:59 AM
[SOLVED] Queries return no records TinyTin Queries 0 07-25-2001 06:27 AM
Dynamic Cross Tab Queries? CutAndPaste Queries 0 07-20-2001 12:43 AM

All times are GMT -8. The time now is 10:52 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World