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 11 4072
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.goo glegroups.com.. . 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
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
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.goo glegroups.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(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
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.goo glegroups.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(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
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 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.goo glegroups.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
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.Thi s.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("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 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
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!!
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: laurie |
last post by:
Hi all,
I'm trying to help out a friend who has inherited a client with a PHP
shopping cart application. Neither of us know PHP, but I've been
muddling my way through, trying to get these old scripts working on a
new server with the most recent version of PHP. I've pretty much
taken care of all the various errors that were popping up. ...
|
by: the_proud_family |
last post by:
HELP ME PLEASE!! my email is the_proud_family@yahoo.com
I can't get the ball to go up right side and then I need it to turn
around and keep turning until velocity=0 I have been at it for the
past 2 weeks now i give up and call for help. Please if anyone can
gide me through i will be so grateful!! I have pasted my code below
|
by: Kurt Watson |
last post by:
I’m having a different kind of problem with Hotmail when I sign in it
says, "Web Browser Software Limitations
Your Current Software Will Limit Your Ability to Use Hotmail
You are using a web browser that Hotmail does not support. If you
continue to use your current browser software we cannot guarantee that
Hotmail will work correctly for...
|
by: Christo |
last post by:
borland c++ 5.01
character constant must be one or two characters long
get this when compiling my first c++ program can anyone out there help?
it is highlighting this line as the problem
cout << "Please Enter First number: ";
|
by: x muzuo |
last post by:
Hi guys,
I have got a prob of javascript form validation which just doesnt work
with my ASP code. Can any one help me out please.
Here is the code:
{////<<head>
<title>IIBO Submit Page</title>
</head>
<style type="text/css">
| |
by: Craig Keightley |
last post by:
Please help, i have attached my page which worksin IE but i cannnot get the
drop down menu to fucntion in firefox. Any one have any ideas why?
Many Thanks
Craig
<<<<<<<<<<<<<<CODE>>>>>>>>>>>>>>>>
<html>
|
by: Jason |
last post by:
Hi,
I was wondering if any could point me to an example or give me ideas on how
to dynamically create a form based on a database table? So, I would have a
table designed to tell my application to create certain textboxes, labels,
and combo boxes? Any ideas would be appreciated.
Thanks
|
by: tabani |
last post by:
I wrote the program and its not giving me correct answer can any one help me with that please and specify my mistake please it will be highly appreciable...
The error arrives from option 'a' it asks for user name, check in the system but does not return the correct answer please help me with it.
or if you have better way of doing it would you...
|
by: =?Utf-8?B?U2NvdHRSYWREZXY=?= |
last post by:
I'm creating a doc project for my c# program. I've done this before but this
time sonething is wrong. I build my doc project and is succeeds but when I
open the help file, there is no documentation there. I enabled XML
documentation file under build properties and have recompiled my program but
still I have nothing. What am I doing wrong?
--...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |