Help with DConcat - Please

**Wayne Morgan** · Nov 13 '05, 09:47 AM

Re: Help with DConcat - Please

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

<kimberly.shaff er@gmail.com> wrote in message
news:1113021739 .488090.102570@ g14g2000cwa.goo glegroups.com.. .[color=blue]
>I think I am so lost!!
>
> Please someone write out DCONCAT explanation.
>
> Is it
>
> DConcat("FieldY ouWantToConcate nate","TableFie ldIsLocatedIn", "WHATIDGOESHERE ",
> "@SignOrOtherDe limiter", NoIdeaWhatGoesH ere)
>
> 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
>[/color]

**kimberly.shaffer@gmail.com** · Nov 13 '05, 09:47 AM

Re: Help with DConcat - Please

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(Criter ia)) > 0 Then SQL = SQL & " Where " & Criteria
If Len(Trim(OrderB y)) > 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.OpenRecordse t(SQL,
dbOpenForwardOn ly)
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** · Nov 13 '05, 09:47 AM

Re: Help with DConcat - Please

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:

Modules: Return a concatenated list of sub-record values

http://www.mvps.org/access/modules/mdl0004.htm

Jeff

<kimberly.shaff er@gmail.com> wrote in message
news:1113024669 .686099.241170@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> 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(Criter ia)) > 0 Then SQL = SQL & " Where " & Criteria
> If Len(Trim(OrderB y)) > 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.OpenRecordse t(SQL,
> dbOpenForwardOn ly)
> 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
>[/color]

**kimberly.shaffer@gmail.com** · Nov 13 '05, 09:48 AM

Re: Help with DConcat - Please

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
Kim@email.com
Pete@email.com
Sasha@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:[color=blue]
> To even make it easier, post the table structure for your two table[/color]
and also[color=blue]
> some sample data. The example you gave doesn't tell us what fields[/color]
and[color=blue]
> records the data came from and how they're supposed (or not[/color]
suppossed) to be[color=blue]
> related.
>
> By what it looks like you're trying to do is this:
> http://www.mvps.org/access/modules/mdl0004.htm
>
> Jeff
>
>
> <kimberly.shaff er@gmail.com> wrote in message
> news:1113024669 .686099.241170@ z14g2000cwz.goo glegroups.com.. .[color=green]
> > Well, first off, I am trying to concatenate a 1:M string. (the[/color][/color]
many[color=blue][color=green]
> > part) where I have two tables, one has 10 records, two fields, the
> > second has 20 records, two fields, where one field contains the[/color][/color]
same[color=blue][color=green]
> > records I can join on. I have created a 1:M relationship and so[/color][/color]
the 10[color=blue][color=green]
> > record table shows like, a two record subtable for each record...[/color][/color]
I am[color=blue][color=green]
> > trying to create a query that shows the 10 records in the first[/color][/color]
field,[color=blue][color=green]
> > and concatenates the records in the subtable to the table. Geez,[/color][/color]
does[color=blue][color=green]
> > that make sense? Anyway, I got it to concatenate, but it[/color][/color]
concatenates[color=blue][color=green]
> > ALL the sub records for the Primary record, not just the sub[/color][/color]
records[color=blue][color=green]
> > 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[/color][/color]
String, _[color=blue][color=green]
> >
> > 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[/color][/color]
" &[color=blue][color=green]
> > Domain _
> > Else SQL = "Select All (" & Expr & ") From " & Domain
> >
> >
> > If Len(Trim(Criter ia)) > 0 Then SQL = SQL & " Where " & Criteria
> > If Len(Trim(OrderB y)) > 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.OpenRecordse t(SQL,
> > dbOpenForwardOn ly)
> > 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 &[/color][/color]
DConcat &[color=blue][color=green]
> > 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
> >[/color][/color]

**Jeff Smith** · Nov 13 '05, 09:48 AM

Re: Help with DConcat - Please

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("t blSecond","fld2 ","fld3","Long" ,[tblFirst].[fld1]) AS Hobbies,
tblFirst.fld3
FROM tblFirst INNER JOIN tblSecond ON tblFirst.fld1 = tblSecond.fld2;

This uses the code from

Modules: Return a concatenated list of sub-record values

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.shaff er@gmail.com> wrote in message
news:1113089065 .702135.140500@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> 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
> Kim@email.com
> Pete@email.com
> Sasha@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:[color=green]
> > To even make it easier, post the table structure for your two table[/color]
> and also[color=green]
> > some sample data. The example you gave doesn't tell us what fields[/color]
> and[color=green]
> > records the data came from and how they're supposed (or not[/color]
> suppossed) to be[color=green]
> > related.
> >
> > By what it looks like you're trying to do is this:
> > http://www.mvps.org/access/modules/mdl0004.htm[/color][/color]

**Jeff Smith** · Nov 13 '05, 09:48 AM

Re: Help with DConcat - Please

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" <NoSpam@Not.Thi s.Address> wrote in message
news:d3b31j$m0h $1@lust.ihug.co .nz...[color=blue]
> 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("t blSecond","fld2 ","fld3","Long" ,[tblFirst].[fld1]) AS[/color]
Hobbies,[color=blue]
> 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[/color]
and[color=blue]
> to also include a space.
>
> Jeff
>[/color]

**kimberly.shaffer@gmail.com** · Nov 13 '05, 09:48 AM

Re: Help with DConcat - Please

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@gmail.com** · Nov 13 '05, 09:49 AM

Re: Help with DConcat - Please

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@gmail.com** · Nov 13 '05, 09:49 AM

Re: Help with DConcat - Please

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** · Nov 13 '05, 09:49 AM

Re: Help with DConcat - Please

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

<kimberly.shaff er@gmail.com> wrote in message
news:1113159878 .286313.54910@g 14g2000cwa.goog legroups.com...[color=blue]
> 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.
>[/color]

**kimberly.shaffer@gmail.com** · Nov 13 '05, 09:49 AM

Re: Help with DConcat - Please

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.

Help with DConcat - Please

Help with DConcat - Please

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment