Complex VBA Concat Related Fields

[June7]

Why is there zero VBA in posted db, not even your original attempted code? Of course function call won't work because there isn't any such function available.



SQL using Allen's code:

SELECT DISTINCT tblOrders.ItemID, tblItem.Item, tblOrders.DateOrdered, 
ConcatRelated("FullName","qryOrderCustomers","ItemID & DateOrdered ='" & [tblOrders].[ItemID] & [DateOrdered] & "'","FullName","; ") AS Customers
FROM tblItem INNER JOIN tblOrders ON tblItem.ItemID = tblOrders.ItemID;

SQL using theDBGuy version:

SELECT DISTINCT tblOrders.ItemID, tblItem.Item, tblOrders.DateOrdered, 
SimpleCSV("SELECT FullName FROM qryOrderCustomers WHERE ItemID & DateOrdered ='" & [tblOrders].[ItemID] & [DateOrdered] & "' ORDER BY FullName","; ") AS Customers
FROM tblItem INNER JOIN tblOrders ON tblItem.ItemID = tblOrders.ItemID;

or without qryOrderCustomers:

SELECT DISTINCT tblOrders.ItemID, tblItem.Item, tblOrders.DateOrdered, 
SimpleCSV("SELECT FullName FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID WHERE ItemID & DateOrdered ='" & [tblOrders].[ItemID] & [DateOrdered] & "' ORDER BY FullName","; ") AS Customers
FROM tblItem INNER JOIN tblOrders ON tblItem.ItemID = tblOrders.ItemID;

ItemID	Item	DateOrdered	Customers
1	Apple	12/9/2024	Fred
1	Apple	12/13/2024	Fred; Mary
2	Banana	12/9/2024	Fred; John

[Gicu]

Have a look at the attached sample qryItemsByDate (sorry June, I didn't see your edited post until just now).
Cheers,

[Pianopizza5]

Thank you so much for your reply. I’ll look at both.

So I’m obviously missing something here. You define the function in the module? How would you call upon that when writing the code in SQL? Or would it be better to call it in VBA?

Thank you so much for writing that code. I tried everyone and got the same Undefined error (which I’m guessing now is because I didn’t define it, haha). How would you define it?

I’m learning all this by myself. Would you recommend any good books or videos/courses.

[Welshgasman]

Plenty of videos on YouTube.

[June7]

You call the concatenation function in query same as any function whether it be intrinsic (Date(), IIf(), etc) or custom. But the function has to first exist so Access can find it whether called from query, textbox, or other procedure.

In this case, custom function must be located in a general module. So first insert a new general module (from the VBA Editor menu bar). Then copy/paste or type function code into module. Now call the function as demonstrated.

Every VBA module should have this line in its header: Option Explicit
Unfortunately, this is not a default setting in Access. So before creating any modules do this:
From VBA Editor > Tools > Options > Editor tab > click Require Variable Declaration

This line would have to be manually added to any existing modules.

[Pianopizza5]

I apologize for such a late reply. This worked! Thank you all so much for your patience and for sharing your knowledge. I learned a lot.