Help with DConcat - Please

[kimberly.shaffer]

I think I am so lost!!

Please someone write out DCONCAT explanation.

Is it

DConcat("FieldYouWantToConcatenate","TableFieldIsL ocatedIn","WHATIDGOESHERE",
"@SignOrOtherDelimiter", NoIdeaWhatGoesHere)

I think I would be okay if I could figure out what the "WHATIDGOESHERE"
is and how it works. Is it supposed to be the primary key or what? I
have really tried to figure this out and no luck. me <-----newbie

[Wayne Morgan]

I've never heard of the function. I'm guessing it is a user defined
function. If so, we'll need to see the code in the function before we will
know what it does and how to use it.

What are you trying to do? There may be another way.

--
Wayne Morgan
MS Access MVP
<ki**************@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I think I am so lost!!

Please someone write out DCONCAT explanation.

Is it

DConcat("FieldYouWantToConcatenate","TableFieldIsL ocatedIn","WHATIDGOESHERE",
"@SignOrOtherDelimiter", NoIdeaWhatGoesHere)

I think I would be okay if I could figure out what the "WHATIDGOESHERE"
is and how it works. Is it supposed to be the primary key or what? I
have really tried to figure this out and no luck. me <-----newbie

[kimberly.shaffer]

Well, first off, I am trying to concatenate a 1:M string. (the many
part) where I have two tables, one has 10 records, two fields, the
second has 20 records, two fields, where one field contains the same
records I can join on. I have created a 1:M relationship and so the 10
record table shows like, a two record subtable for each record... I am
trying to create a query that shows the 10 records in the first field,
and concatenates the records in the subtable to the table. Geez, does
that make sense? Anyway, I got it to concatenate, but it concatenates
ALL the sub records for the Primary record, not just the sub records
for each record. So it's supposed to look like this:

Pete Petersen Saxophone Daddy
but it looks like this

Pete Petersen Saxophone Daddy Actress MathWiz

.............................
This is the code

Public Function DConcat(ByVal Expr As String, ByVal Domain As String, _

Optional ByVal Criteria As String = vbNullString, _
Optional ByVal OrderBy As String = vbNullString, _
Optional ByVal Separator As String = " ", _
Optional ByVal DistinctValues As Boolean = False) As String
On Error GoTo DConcat_Error
If InStr(1, Domain, "[", vbBinaryCompare) = 0 Then Domain = "[" &
Domain & "]"
Dim SQL As String
If DistinctValues Then SQL = "Select Distinct (" & Expr & ") From " &
Domain _
Else SQL = "Select All (" & Expr & ") From " & Domain
If Len(Trim(Criteria)) > 0 Then SQL = SQL & " Where " & Criteria
If Len(Trim(OrderBy)) > 0 Then SQL = SQL & " Order By " & OrderBy
Dim DB As DAO.Database: Set DB = DBEngine(0)(0)
Dim RS As DAO.Recordset: Set RS = DB.OpenRecordset(SQL,
dbOpenForwardOnly)
Dim RF As DAO.Field: Set RF = RS(0)
Dim DCLen As Long
Do Until RS.EOF
Dim R As String
If DCLen Then R = Separator & RF Else R = RF & vbNullString
If DCLen + Len(R) > Len(DConcat) Then DConcat = DConcat & DConcat &
R
Mid$(DConcat, DCLen + 1&) = R
DCLen = DCLen + Len(R)
RS.MoveNext
Loop
If DCLen < Len(DConcat) Then DConcat = Left$(DConcat, DCLen)
Set RF = Nothing
RS.Close: Set RS = Nothing
Set DB = Nothing
Exit Function
DConcat_Error:
Dim EN As Long: EN = Err.Number
Dim ED As String: ED = Err.Description
If Not (RF Is Nothing) Then Set RF = Nothing
If Not (RS Is Nothing) Then RS.Close: Set RS = Nothing
If Not (DB Is Nothing) Then Set DB = Nothing
Err.Raise Number:=EN, Description:=ED
End Function

[Jeff Smith]

To even make it easier, post the table structure for your two table and also
some sample data. The example you gave doesn't tell us what fields and
records the data came from and how they're supposed (or not suppossed) to be
related.

By what it looks like you're trying to do is this:
http://www.mvps.org/access/modules/mdl0004.htm

Jeff
<ki**************@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Well, first off, I am trying to concatenate a 1:M string. (the many
part) where I have two tables, one has 10 records, two fields, the
second has 20 records, two fields, where one field contains the same
records I can join on. I have created a 1:M relationship and so the 10
record table shows like, a two record subtable for each record... I am
trying to create a query that shows the 10 records in the first field,
and concatenates the records in the subtable to the table. Geez, does
that make sense? Anyway, I got it to concatenate, but it concatenates
ALL the sub records for the Primary record, not just the sub records
for each record. So it's supposed to look like this:

Pete Petersen Saxophone Daddy
but it looks like this

Pete Petersen Saxophone Daddy Actress MathWiz

............................
This is the code

Public Function DConcat(ByVal Expr As String, ByVal Domain As String, _

Optional ByVal Criteria As String = vbNullString, _
Optional ByVal OrderBy As String = vbNullString, _
Optional ByVal Separator As String = " ", _
Optional ByVal DistinctValues As Boolean = False) As String
On Error GoTo DConcat_Error
If InStr(1, Domain, "[", vbBinaryCompare) = 0 Then Domain = "[" &
Domain & "]"
Dim SQL As String
If DistinctValues Then SQL = "Select Distinct (" & Expr & ") From " &
Domain _
Else SQL = "Select All (" & Expr & ") From " & Domain
If Len(Trim(Criteria)) > 0 Then SQL = SQL & " Where " & Criteria
If Len(Trim(OrderBy)) > 0 Then SQL = SQL & " Order By " & OrderBy
Dim DB As DAO.Database: Set DB = DBEngine(0)(0)
Dim RS As DAO.Recordset: Set RS = DB.OpenRecordset(SQL,
dbOpenForwardOnly)
Dim RF As DAO.Field: Set RF = RS(0)
Dim DCLen As Long
Do Until RS.EOF
Dim R As String
If DCLen Then R = Separator & RF Else R = RF & vbNullString
If DCLen + Len(R) > Len(DConcat) Then DConcat = DConcat & DConcat &
R
Mid$(DConcat, DCLen + 1&) = R
DCLen = DCLen + Len(R)
RS.MoveNext
Loop
If DCLen < Len(DConcat) Then DConcat = Left$(DConcat, DCLen)
Set RF = Nothing
RS.Close: Set RS = Nothing
Set DB = Nothing
Exit Function
DConcat_Error:
Dim EN As Long: EN = Err.Number
Dim ED As String: ED = Err.Description
If Not (RF Is Nothing) Then Set RF = Nothing
If Not (RS Is Nothing) Then RS.Close: Set RS = Nothing
If Not (DB Is Nothing) Then Set DB = Nothing
Err.Raise Number:=EN, Description:=ED
End Function

[kimberly.shaffer]

Yes, I've tried that module, even with the sample databases, and it
always came up null for me in that field.

Okay, here goes and I will post a little differently to make it more
easier to understand:

tblfirst
fld1 - Primary key - autonumber
fld2 - text - People IDs
E11111
E22222
E33333
fld3 - text - People Emails
Ki*@email.com
Pe**@email.com
Sa***@email.com

tblsecond
fld1
Primary key - autonumber

fld2 - text - People IDs
E11111
E11111
E22222
E22222
E33333
E33333

fld3 - text - People Hobbies
Sax
Piano
Clarinet
Harp
Harmonica
Flute

The tables are joined in a 1:M with tblFirst being the 1 and tblSecond
being the many.

We will say each person has 2 hobbies and I want to join these two
tables and then concatenate the two hobbies, so instead of seeing:

E11111 Sax
E11111 Piano
E22222 Clarinet
E22222 Harp
E33333 Harmonica
E33333 Flute

I want to see
Query3
Fld1 Fld2
E11111 Sax, Piano
E22222 Clarinet, Piano
E33333 Harmonica, Flute

and then be able to add the email addresses as a third field if I want
to.

I hope this makes it clearer what I want to do.

Jeff Smith wrote:

To even make it easier, post the table structure for your two table and also some sample data. The example you gave doesn't tell us what fields and records the data came from and how they're supposed (or not suppossed) to be related.

By what it looks like you're trying to do is this:
http://www.mvps.org/access/modules/mdl0004.htm

Jeff
<ki**************@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Well, first off, I am trying to concatenate a 1:M string. (the many part) where I have two tables, one has 10 records, two fields, the
second has 20 records, two fields, where one field contains the same records I can join on. I have created a 1:M relationship and so the 10 record table shows like, a two record subtable for each record... I am trying to create a query that shows the 10 records in the first field, and concatenates the records in the subtable to the table. Geez, does that make sense? Anyway, I got it to concatenate, but it concatenates ALL the sub records for the Primary record, not just the sub records for each record. So it's supposed to look like this:

Pete Petersen Saxophone Daddy
but it looks like this

Pete Petersen Saxophone Daddy Actress MathWiz

............................
This is the code

Public Function DConcat(ByVal Expr As String, ByVal Domain As String, _
Optional ByVal Criteria As String = vbNullString, _
Optional ByVal OrderBy As String = vbNullString, _
Optional ByVal Separator As String = " ", _
Optional ByVal DistinctValues As Boolean = False) As String
On Error GoTo DConcat_Error
If InStr(1, Domain, "[", vbBinaryCompare) = 0 Then Domain = "[" &
Domain & "]"
Dim SQL As String
If DistinctValues Then SQL = "Select Distinct (" & Expr & ") From " & Domain _
Else SQL = "Select All (" & Expr & ") From " & Domain
If Len(Trim(Criteria)) > 0 Then SQL = SQL & " Where " & Criteria
If Len(Trim(OrderBy)) > 0 Then SQL = SQL & " Order By " & OrderBy
Dim DB As DAO.Database: Set DB = DBEngine(0)(0)
Dim RS As DAO.Recordset: Set RS = DB.OpenRecordset(SQL,
dbOpenForwardOnly)
Dim RF As DAO.Field: Set RF = RS(0)
Dim DCLen As Long
Do Until RS.EOF
Dim R As String
If DCLen Then R = Separator & RF Else R = RF & vbNullString
If DCLen + Len(R) > Len(DConcat) Then DConcat = DConcat & DConcat & R
Mid$(DConcat, DCLen + 1&) = R
DCLen = DCLen + Len(R)
RS.MoveNext
Loop
If DCLen < Len(DConcat) Then DConcat = Left$(DConcat, DCLen)
Set RF = Nothing
RS.Close: Set RS = Nothing
Set DB = Nothing
Exit Function
DConcat_Error:
Dim EN As Long: EN = Err.Number
Dim ED As String: ED = Err.Description
If Not (RF Is Nothing) Then Set RF = Nothing
If Not (RS Is Nothing) Then RS.Close: Set RS = Nothing
If Not (DB Is Nothing) Then Set DB = Nothing
Err.Raise Number:=EN, Description:=ED
End Function

[Jeff Smith]

Hi Kimberly
Using the sample data and sample table structure you've given, paste this
SQL statement into your query:
SELECT DISTINCT tblFirst.fld2,
fConcatChild("tblSecond","fld2","fld3","Long",[tblFirst].[fld1]) AS Hobbies,
tblFirst.fld3
FROM tblFirst INNER JOIN tblSecond ON tblFirst.fld1 = tblSecond.fld2;

This uses the code from
http://www.mvps.org/access/modules/mdl0004.htm
You may need to modify it a little to change the semicolon into a comma and
to also include a space.

Jeff
<ki**************@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Yes, I've tried that module, even with the sample databases, and it
always came up null for me in that field.

Okay, here goes and I will post a little differently to make it more
easier to understand:

tblfirst
fld1 - Primary key - autonumber
fld2 - text - People IDs
E11111
E22222
E33333
fld3 - text - People Emails
Ki*@email.com
Pe**@email.com
Sa***@email.com

tblsecond
fld1
Primary key - autonumber

fld2 - text - People IDs
E11111
E11111
E22222
E22222
E33333
E33333

fld3 - text - People Hobbies
Sax
Piano
Clarinet
Harp
Harmonica
Flute

The tables are joined in a 1:M with tblFirst being the 1 and tblSecond
being the many.

We will say each person has 2 hobbies and I want to join these two
tables and then concatenate the two hobbies, so instead of seeing:

E11111 Sax
E11111 Piano
E22222 Clarinet
E22222 Harp
E33333 Harmonica
E33333 Flute

I want to see
Query3
Fld1 Fld2
E11111 Sax, Piano
E22222 Clarinet, Piano
E33333 Harmonica, Flute

and then be able to add the email addresses as a third field if I want
to.

I hope this makes it clearer what I want to do.

Jeff Smith wrote:

To even make it easier, post the table structure for your two table

and also

some sample data. The example you gave doesn't tell us what fields

and

records the data came from and how they're supposed (or not

suppossed) to be

related.

By what it looks like you're trying to do is this:
http://www.mvps.org/access/modules/mdl0004.htm

[Jeff Smith]

Here's the solution to the code you had posted using the same table
structure and data as before.

SELECT DISTINCT tblFirst.fld2, DConcat("fld3","tblSecond","[fld2]=" &
[tblFirst].[fld1]) AS Hobies, tblFirst.fld3
FROM tblFirst INNER JOIN tblSecond ON tblFirst.fld1 = tblSecond.fld2;

Jeff
"Jeff Smith" <No****@Not.This.Address> wrote in message
news:d3**********@lust.ihug.co.nz...

Hi Kimberly
Using the sample data and sample table structure you've given, paste this
SQL statement into your query:
SELECT DISTINCT tblFirst.fld2,
fConcatChild("tblSecond","fld2","fld3","Long",[tblFirst].[fld1]) AS Hobbies, tblFirst.fld3
FROM tblFirst INNER JOIN tblSecond ON tblFirst.fld1 = tblSecond.fld2;

This uses the code from
http://www.mvps.org/access/modules/mdl0004.htm
You may need to modify it a little to change the semicolon into a comma and to also include a space.

Jeff

[kimberly.shaffer]

I am still getting no values! Argh! What am I doing wrong? What
should I be joining my tables on in relationships? Maybe that's what
I'm doing wrong. This is so frustrating!!

[kimberly.shaffer]

Oh my geez, I got it to work, and saved it but now I have to look at it
and figure out how it works. This is very very complicated!!!! But
thanks so much.

[kimberly.shaffer]

This is the final code and instructs that finally worked for me.

Create two tables:
tblFirst and tblSecond

tblFirst has three fields with the following data

Fld1(autonumber) Fld2 Fld3
-----------------------------------------------------------------------
1 E11111 Fred
2 E22222 Wilma
3 E33333 Barney
4 E44444 Betty
5 E55555 Dino

tblSecond has three fields with the following data

Fld1(autonumber) Fld2 Fld3
------------------------------------------------------------------------
1 E11111 Bowling
2 E11111 Daddy
3 E22222 Pearls
4 E22222 Mommy
5 E33333 Bowling
6 E33333 Friend
7 E44444 Wife
8 E44444 Blue Dress
9 E55555 Pet
10 E55555 Outside

I changed the Primary Key on tblFirst to Fld2 and Primary Key on
tblSecond to Fld1, then joined tblFirst 1:M to tblSecond using fld2
from tblFirst to fld2 on tblSecond in the relationship box. I checked
Enforce Referential Integrity, Cascade Update Related Fields, and
Cascade Delete Related Records.

I then created a query with an expression using DConcat by Joe Foster
(posted above)

In Design View it looks like

ConcatField: dconcat("[fld3]","[tblSecond]","[Fld2]='" & [Fld2] & "'")
(there is only table it calls from - tblSecond)

In SQL it looks like

SELECT tblSecond.fld2, dconcat("[fld3]","[tblSecond]","[Fld2]='" &
[Fld2] & "'") AS ConcatField
FROM tblSecond GROUP BY tblSecond.fld2;

I wrestled with this for many many days. I hope that my step by step
will help someone else out.

[Jeff Smith]

It was a bit difficult to get it working properly because of how you named
your fields in your example and also how you've structured the tables.
Here's how I would have structured the tables.

tblPeople
PersonID (Autonumber) PK
Identifier
FirstName
Email

tblHobbies
HobbyID (Autonumber) PK
PersonID FK (linked to tblPerson)
Hobby
<ki**************@gmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

This is the final code and instructs that finally worked for me.

Create two tables:
tblFirst and tblSecond

tblFirst has three fields with the following data

Fld1(autonumber) Fld2 Fld3
-----------------------------------------------------------------------
1 E11111 Fred
2 E22222 Wilma
3 E33333 Barney
4 E44444 Betty
5 E55555 Dino

tblSecond has three fields with the following data

Fld1(autonumber) Fld2 Fld3
------------------------------------------------------------------------
1 E11111 Bowling
2 E11111 Daddy
3 E22222 Pearls
4 E22222 Mommy
5 E33333 Bowling
6 E33333 Friend
7 E44444 Wife
8 E44444 Blue Dress
9 E55555 Pet
10 E55555 Outside

I changed the Primary Key on tblFirst to Fld2 and Primary Key on
tblSecond to Fld1, then joined tblFirst 1:M to tblSecond using fld2
from tblFirst to fld2 on tblSecond in the relationship box. I checked
Enforce Referential Integrity, Cascade Update Related Fields, and
Cascade Delete Related Records.

I then created a query with an expression using DConcat by Joe Foster
(posted above)

In Design View it looks like

ConcatField: dconcat("[fld3]","[tblSecond]","[Fld2]='" & [Fld2] & "'")
(there is only table it calls from - tblSecond)

In SQL it looks like

SELECT tblSecond.fld2, dconcat("[fld3]","[tblSecond]","[Fld2]='" &
[Fld2] & "'") AS ConcatField
FROM tblSecond GROUP BY tblSecond.fld2;

I wrestled with this for many many days. I hope that my step by step
will help someone else out.

[kimberly.shaffer]

I know, but the table I am actually working with as my [tblSecond]
concat table has 3000+ records and the fields that have to be linked
can't be autonumbered - they are coming in via odbc so I guess that
makes it really hard too. You have no idea how much you have helped me
though. Thank you so very much.