Concatenate Records with criteria

Hi Experts

I have the following function that I am calling through a query. It works fine except that I wanted it to combine with the CommodityID field (which it is doing) but also the SID field.

The function:

Dim rst As DAO.Recordset
Dim strCombinePD As String

Set rst = CurrentDb.OpenRecordset("SELECT [" & strGetField & "] FROM [" & strTable & "] WHERE [" & strIDField & "]=" & strIDValue)

While Not rst.EOF
   strCombinePD = strCombinePD & rst(strGetField) & " "
Set rst = Nothing
If strCombinePD <> " " Then strCombinePD = Left$(strCombinePD, Len(strCombinePD) - 1)
shnCombinePD = strCombinePD

How I am calling it in the query:

BPDC: shnCombinePD("tblPDSMS","CommodityId",tblPDSMS.CommodityId,"BuyerCodePD")

This is combining all the BuyerCodePD according to the CommodityID but I would like it to use the SID field as the criteria as well.

It is late here and I cannot see the forrest for the trees :). Thanks in advance for something that I imagine is very simple :)

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Thanks in advance for something that I imagine is very simple :)>>

Your passing four arguments, so your function needs to accept four arguments.   Post the declaration (Public Function shnCombinePD (....) for the procedure.

As Jim said, without the header, we can't match up the arguments.  

As I look at this though, I wonder at the horror of a schema that requires such a thing.  Are you certain you can't do this with a join?  Do you really need a function that has to open a recordset of a table whose name it doesn't even know ahead of time?  Have you perhaps abstracted this too much?
Helen FeddemaCommented:
Without seeing your tables, it is hard to know, but my recent article on Complex Concatenation might be helpful:

It illustrates concatenating values from a field, with filters based on other fields.  Here is the filtered report:

Complex Concatenation report
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

The link to your article doesn't work for me.
ajaeclarkeAuthor Commented:
Sorry for the late reply. Got called away un-expectantly.

Jim Dettman, here is the declaration:

Function shnCombinePD(strTable As String, strIDField As String, strIDValue As Long, strGetField As String) As String

PatHartman, sorry but I don't find it a 'horror' of a schema and no, I cannot do it by a join.

Helen, sorry the link doesn't work for me either.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Function shnCombinePD(strTable As String, strIDField As String, strIDValue As Long, strGetField As String) As String>>

 OK, so your procedure right now is accepting a table, a field name for a criteria check, value to check for on that field, and the field name you want combined.

 In terms of criteria, you'd be better off to pass the entire "WHERE" clause at one time.  The problem with passing in the fields themselves, you never know how many there are, the data type or each (number, text, or date), and the conditional checks that need to be performed.   To see what I'm talking about, look at "Searching for faster lookup techniques"  here:

At it's simplest though, you could simply extended this further by adding a second field name/value pair of arguments and building the WHERE clause up:

Function shnCombinePD(strTable As String, strIDFieldA As String, strIDValueA As Long, strIDFieldB As String, strIDValueB as string, strGetField As String) As String


Dim rst As DAO.Recordset
Dim strCriteria as string
Dim strCombinePD As String

strCriteria = ""

If strIDFieldA<>"" then
   strCriteria = strCriteria & " AND [" & strIDFieldA & "] = " & strIDValueA
End If

If strIDFieldB<>"" then
   strCriteria = strCriteria & " AND [" & strIDFieldB & "] = " & strIDValueB
End If

If strCriteria ="" then
  Set rst = CurrentDb.OpenRecordset("SELECT [" & strGetField & "] FROM [" & strTable & "]
  strCriteria = Mid$(strCriteria,6)
  Set rst = CurrentDb.OpenRecordset("SELECT [" & strGetField & "] FROM [" & strTable & "] WHERE " & strCriteria)
End If

 and that would get you what you want.   Understand that there are any number of ways you might structure this.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
ajaeclarkeAuthor Commented:
Thanks for the detailed reply Jim. It works a treat.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.