Link to home
Start Free TrialLog in
Avatar of ajaeclarke
ajaeclarke

asked on

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) & " "
   rst.MoveNext
Wend
rst.Close
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 :)

Ajae
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<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.

Jim.
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?
Without seeing your tables, it is hard to know, but my recent article on Complex Concatenation might be helpful:

http://www.helenfeddema.com/Files/accarch241.zip

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

User generated image
Helen,
The link to your article doesn't work for me.
Thanks,
Pat
Avatar of ajaeclarke
ajaeclarke

ASKER

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.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the detailed reply Jim. It works a treat.