Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 11 Access 2021
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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:
    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:
    Code:
    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:
    Code:
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #17
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,235
    Have a look at the attached sample qryItemsByDate (sorry June, I didn't see your edited post until just now).
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #18
    Pianopizza5 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2019
    Posts
    46
    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.

  4. #19
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,258
    Plenty of videos on YouTube.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #20
    June7's Avatar
    June7 is offline VIP
    Windows 11 Access 2021
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #21
    Pianopizza5 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2019
    Posts
    46
    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. CRLF automatically appended in string concat?
    By tnt in forum Programming
    Replies: 2
    Last Post: 10-04-2011, 09:36 PM
  2. Concat field names in Update SQL
    By Deutz in forum Queries
    Replies: 8
    Last Post: 09-21-2011, 05:43 PM
  3. Group concat
    By iostream in forum Queries
    Replies: 1
    Last Post: 02-23-2011, 12:29 PM
  4. Concat in SQL creates unwanted spaces
    By Deutz in forum Access
    Replies: 3
    Last Post: 12-07-2010, 11:43 PM
  5. concat problem
    By leahcim_32 in forum Access
    Replies: 1
    Last Post: 08-28-2009, 05:31 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums