Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2015
    Posts
    20

    Unanswered: How to remove duplicates from union query

    This is going to be long, so I'm sorry. I've been working on this for over a week now, can't seem to come close to finding a solution.

    My database keeps track of customers, buildings, communications rooms, cabinets (inside rooms) and equipment (inside cabinets). I require a union query because a customer can either be a point of contact for a building (facility manager) or a POC for a room. You can see the relationships below.

    Click image for larger version. 

Name:	relationship2.0.png 
Views:	6 
Size:	46.8 KB 
ID:	16578
    I have a union query which queries pretty much most of my database. I use this query as the RecordSource for a search form. The form uses several search boxes to filter out results. Between customers, buildings, rooms, and equipment. I need to be able to search by Customer's information (last name, first name, organization, etc), building name, room name, and also by equipment information (equipment type, brand, name, IP address, etc). The purpose of this search form is to return the Building ID and the Room ID and store them in two separate text boxes. This is because Building ID and Room ID is related to customers as well as cabinets and equipment. Whenever I filter out by any of the above criteria, I need to see the building ID and the room ID in txtBuildingID and txtRoomsID. I then have several list boxes which get their data from a query using those two text boxes in the WHERE clause. This is because whenever I search, I need to see the building ID and the Room ID and ALL related data.

    Here is the SQL for the union query:
    Code:
    SELECT tblCustomer.OrganizationFK, tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblCustomer.LastName, tblCustomer.FirstName, tblFacilityMgr.BuildingFK, tblRooms.RoomsPK, tblBuilding.BuildingName, tblRooms.RoomName, tblSecOptions.SecName, tblCabinet.CabinetName, tblEquipment.CabinetFK, tblEquipment.EquipmentNameFK, tblEquipment.EquipmentBrandFK, tblEquipment.EquipmentNetworkTypeFK, tblEquipment.EquipmentIP, tblEquipment.SerialNum
    FROM tblSecOptions INNER JOIN (((tblBuilding INNER JOIN (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK) INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN (tblCabinet LEFT JOIN tblEquipment ON tblCabinet.CabinetPK = tblEquipment.CabinetFK) ON tblRooms.RoomsPK = tblCabinet.RoomsFK) ON tblSecOptions.SecOptionPK = tblRooms.SecOptionFK
    UNION SELECT tblCustomer.OrganizationFK, tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblCustomer.LastName, tblCustomer.FirstName, tblRooms.BuildingFK, tblRooms.RoomsPK, tblBuilding.BuildingName, tblRooms.RoomName, tblSecOptions.SecName, tblCabinet.CabinetName, tblEquipment.CabinetFK, tblEquipment.EquipmentNameFK, tblEquipment.EquipmentBrandFK, tblEquipment.EquipmentNetworkTypeFK, tblEquipment.EquipmentIP, tblEquipment.SerialNum
    FROM tblSecOptions INNER JOIN (((tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) LEFT JOIN (tblCabinet LEFT JOIN tblEquipment ON tblCabinet.CabinetPK = tblEquipment.CabinetFK) ON tblRooms.RoomsPK = tblCabinet.RoomsFK) INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK) ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) ON tblSecOptions.SecOptionPK = tblRooms.SecOptionFK;
    Now, due to the nature of the union query, how it queries all information and then is filtered out, there is lots of duplicate data. Suppose if Building A, Room 1100 is owned by "Michael Smith" but that room is also owned by "John Jones" I would then see two records which both share the same building ID and room ID. The problem is, I need records in the union query to contain duplicates because if the same building/room is owned by multiple people, I need to see all that in the query. Reason being, I need to be able to search by BOTH "Michael Smith" as well as "John Jones."

    So, best I can figure the duplicate records need to be filtered out of the recordsource after the form is filtered by my search boxes. I am not sure how to do this but I believe I would use DCount. It is important that the combination txtBuildingID and txtRoomsID be unique. This is because after a search has been made, there may be many of the same building ID, but different room IDs. An example, if I searched for "Michael Smith" and he owned building ID 1, room ID 1 / building ID 1, room ID 2 / building ID 1, room ID 3, I should then see three records. All with the same building ID, but different room IDs.

    Here is the VBA code for my database:

    Code:
    Option Compare Database
    Option Explicit  'always set this  It will point out errors with field/vaiable names
    
    
    Private Sub cboSearchLastName_AfterUpdate()
        Me.cboSearchFirstName.Requery
    End Sub
    
    Private Sub cboSearchOrganization_AfterUpdate()
        Me.cboSearchShopName.Requery
    End Sub
    
    Private Sub cboSearchShopName_AfterUpdate()
        Me.cboSearchOfficeSym.Requery
    End Sub
    
    Private Sub cmdReset_Click()
        Me.cboSearchBuildingName = ""
        Me.cboSearchRoomName = ""
        Me.cboSearchOrganization = ""
        Me.cboSearchShopName = ""
        Me.cboSearchOfficeSym = ""
        Me.cboSearchLastName = ""
        Me.cboSearchFirstName = ""
        Me.FilterOn = False
    End Sub
    Private Sub Form_Current()
        Me.lstFacilityMgr.Requery
        Me.lstRoomsPOC.Requery
    End Sub
    
    Private Sub cmdSearch_Click()
        Dim strWhere As String
        Dim lngLen As Long
        Dim startStr As String
        If Not IsNullOrEmpty(Me.cboSearchLastName) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[LastName] ='" & Me.cboSearchLastName & "'"
        End If
        If Not IsNullOrEmpty(Me.cboSearchFirstName) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[FirstName] ='" & Me.cboSearchFirstName & "'"
        End If
        If Not IsNullOrEmpty(Me.cboSearchOrganization) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[OrganizationFK] =" & Me.cboSearchOrganization
        End If
        If Not IsNullOrEmpty(Me.cboSearchShopName) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[ShopNameFK] =" & Me.cboSearchShopName
        End If
        If Not IsNullOrEmpty(Me.cboSearchOfficeSym) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[OfficeSymFK] =" & Me.cboSearchOfficeSym
        End If
        If Not IsNullOrEmpty(Me.cboSearchBuildingName) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[BuildingFK] =" & Me.cboSearchBuildingName
        End If
        If Not IsNullOrEmpty(Me.cboSearchRoomName) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[RoomsPK] =" & Me.cboSearchRoomName
        End If
        If Not IsNullOrEmpty(Me.cboSearchEquipmentName) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[EquipmentNameFK] =" & Me.cboSearchEquipmentName
        End If
        If Not IsNullOrEmpty(Me.cboSearchEquipmentSerialNum) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[SerialNum] ='" & Me.cboSearchEquipmentSerialNum & "'"
        End If
            If Not IsNullOrEmpty(Me.cboSearchEquipmentIP) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[EquipmentIP] ='" & Me.cboSearchEquipmentIP & "'"
        End If
        Call MsgBox(strWhere, vbOKOnly, "Debug")
        lngLen = Len(strWhere) - 5
        If lngLen <= 0 Then
            MsgBox "No criteria", vbInformation, "Nothing to do."
        Else
            'strWhere = Left$(strWhere, lngLen)
            MsgBox strWhere
             If DCount("*", "qryRecordSet", strWhere) = 0 Then
                    MsgBox "No corresponding records to your search criteria." & vbCrLf & vbCrLf
                    Me.FilterOn = False
                    Me.cboSearchBuildingName = ""
                    Me.cboSearchRoomName = ""
                    Me.cboSearchOrganization = ""
                    Me.cboSearchShopName = ""
                    Me.cboSearchOfficeSym = ""
                    Me.cboSearchLastName = ""
                    Me.cboSearchFirstName = ""
             Else
                Me.Filter = strWhere
                Me.FilterOn = True
            End If
        End If
    End Sub
    
    
    
    Function IsNullOrEmpty(val As Variant) As Boolean
       'First conditional validates for Nothing
       'Second condition validates for an Empty String situation "" or "     "
       Dim ret As Boolean: ret = False
       If IsMissing(val) Then
          ret = True
       ElseIf (val Is Nothing) Then
          ret = True
       ElseIf (val & vbNullString = vbNullString) Then
          ret = True
       ElseIf (Len(Trim(val)) <= 0) Then
          ret = True
       End If
     
       IsNullOrEmpty = ret
    End Function
    Finally, here is a screenshot of a search showing 3 records, when it should only show 1:

    Click image for larger version. 

Name:	duplicates.jpg 
Views:	2 
Size:	110.9 KB 
ID:	16579

    Thank you for your help!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a left join, bear in mind that multiple Access LEFT JOINS require its peculiar syntax with lots and lots of bracketing

    you may need to redefine your filter/query elements to allow for the specified value OR NULL. EG:-
    Code:
        If Not IsNullOrEmpty(Me.cboSearchEquipmentSerialNum) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "( [SerialNum] ='" & Me.cboSearchEquipmentSerialNum & "' OR ISNULL([SerialNum])"
        endif
    ..to cater for the situation where the LEFT JOIN would return a NULL value in the column on the RHS of a join if there was no match

    incidentally you can take advantage of a dodge in SQL to simplify your code to build the where clause

    Code:
        strWhere = " Where 1 = 1 "
        If Not IsNullOrEmpty(Me.cboSearchLastName) Then
            strWhere = strWhere  & " AND [LastName] ='" & Me.cboSearchLastName & "'"
        End If
        If Not IsNullOrEmpty(Me.cboSearchFirstName) Then
            strWhere = strWhere  & " AND [FirstName] ='" & Me.cboSearchFirstName & "'"
        End If
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2015
    Posts
    20
    Thank you for your reply.

    I'm not so sure left join is going to solve my problem. I probably should have been more specific in the tittle, I apologize.

    I suppose the query itself is good just the way it is. It contains duplicates, and I need it to contain duplicates. Please see photo below.

    Click image for larger version. 

Name:	qryRecordSet.png 
Views:	2 
Size:	40.0 KB 
ID:	16580

    As you can see, buildingFK 1, RoomsPK 10 is owned by "DerpyDerp, Snuffy, and Smith." As such there are three records for buildingFK 1, RoomsPK 10. There needs to be duplicates because I need to be able to find that building ID and room ID by searching for either of those 3 people.

    Also if you look at the union query above in my 1st post, you'll see I do use a left join between cabinets and equipment. The reason for this is because not all rooms (or more accurate, cabinets in rooms) have equipment. I need to see all results, whether they have equipment or not. As you can imagine, this creates even more duplicates.

    That said, I've played around with using left join. Doesn't seem to solve my problem. I've also tried "Where 1 = 1", I can't get the syntax right. It says "missing operator in query expression." However, I don't think thats the answer either. The problem is I don't just need to see unique results, I need to see results that are unique between tblFacilitymgr.BuildingFK and tblRooms.RoomsPK. Or two text boxes which are bound to those fields, txtBuildingID and txtRoomsID.

    So basically, I believe I need to:

    1. Leave the query like so.
    2. Filter the query using multiple search criteria (which may or may not be setup correctly).
    3. Filter the filtered results (strWhere) to remove all but one record with a unique combination of txtBuildingID and txtRoomsID.

    Hope that makes sense. I appreciate the help.

  4. #4
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    it,

    I think what you need is one of two things:

    1) Sub-Reports to show the building owner & Room Owner information

    2) A function that will "concatenate" the owner information.


    I think the 2nd option is better for this.

    Make a public function that will concatenate the owners based on
    the building id.

    Make a similar public function for the room owners.

    Put these functions in your query AND remove the joins to the
    building/room owner tables.

    You should be able to do a quick search for +concatenate +function
    and find what you need to get started.

    Put the owner info in the Heading for the GROUP represented by the
    buildings and rooms and that should get rid of your extra rows.

    hth,
    Wayne

  5. #5
    Join Date
    Aug 2015
    Posts
    20
    By sub report, do you mean sub form? How would a subform remove duplicates?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So strWhere is actually a variable used to set a filter. So a suggestion call it something that reflects that... strFilter perhaps . When i saw it I had presumed you were building the query on the fly and strWhere was the fragment containing the WHERE clause

    The 1=1 dodge works in a WHERE clause, i cant see why it wouldnt also work as a filter, but because you are using it in a filter drop the WHERE bit. The logic behind 1=1 is that it always evaluates as true so no matter what options a user chooses or not to filter on the filter text will always have something that evaluates true.
    Essentially a filter is a where clause without using the word where
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2015
    Posts
    20
    Thank you for your help healdem.

    I literally started learning about access,databases, sql, and vba (and programming in general)... 2 months ago. So my terminology might not be the best. I will change it to strFilter when I get time.

    Here is my code now:

    Code:
    Private Sub cmdSearch_Click()
        Dim strWhere As String
        Dim startStr As String
        strWhere = "1 = 1"
        If Not IsNullOrEmpty(Me.cboSearchLastName) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[LastName] ='" & Me.cboSearchLastName & "'"
        End If
        If Not IsNullOrEmpty(Me.cboSearchFirstName) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[FirstName] ='" & Me.cboSearchFirstName & "'"
        End If
        If Not IsNullOrEmpty(Me.cboSearchOrganization) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[OrganizationFK] =" & Me.cboSearchOrganization
        End If
        If Not IsNullOrEmpty(Me.cboSearchShopName) Then
           startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[ShopNameFK] =" & Me.cboSearchShopName
        End If
        If Not IsNullOrEmpty(Me.cboSearchOfficeSym) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[OfficeSymFK] =" & Me.cboSearchOfficeSym
        End If
        If Not IsNullOrEmpty(Me.cboSearchBuildingName) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[BuildingFK] =" & Me.cboSearchBuildingName
        End If
        If Not IsNullOrEmpty(Me.cboSearchRoomName) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[RoomsPK] =" & Me.cboSearchRoomName
        End If
        If Not IsNullOrEmpty(Me.cboSearchEquipmentName) Then
            startStr = IIf(strWhere = "", "", " AND ")
           strWhere = strWhere & startStr & "[EquipmentNameFK] =" & Me.cboSearchEquipmentName
        End If
        If Not IsNullOrEmpty(Me.cboSearchEquipmentSerialNum) Then
           startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[SerialNum] ='" & Me.cboSearchEquipmentSerialNum & "'"
       End If
            If Not IsNullOrEmpty(Me.cboSearchEquipmentIP) Then
            startStr = IIf(strWhere = "", "", " AND ")
            strWhere = strWhere & startStr & "[EquipmentIP] ='" & Me.cboSearchEquipmentIP & "'"
        End If
        Call MsgBox(strWhere, vbOKOnly, "Debug")
            MsgBox strWhere
             If DCount("*", "qryRecordSet", strWhere) = 0 Then
                    MsgBox "No corresponding records to your search criteria." & vbCrLf & vbCrLf
                    Me.FilterOn = False
                    Me.cboSearchBuildingName = ""
                    Me.cboSearchRoomName = ""
                    Me.cboSearchOrganization = ""
                    Me.cboSearchShopName = ""
                    Me.cboSearchOfficeSym = ""
                    Me.cboSearchLastName = ""
                    Me.cboSearchFirstName = ""
             Else
                Me.Filter = strWhere
                Me.FilterOn = True
            End If
    End Sub
    That still returns the same number of duplicate results. I feel I'm doing something wrong.

  8. #8
    Join Date
    Aug 2015
    Posts
    20
    I'm thinking this has to be done w/ dcount. The problem though is:

    1. Figuring out how to get the syntax correct for using two fields instead of 1 (which typically, on the forums, there are people w/ 1 field examples).

    2. Figuring out what the code is to delete the records which are duplicates. And I suppose I might have to tell it which record to delete. Really, I don't care so long as they're deleted.

    Here's some code I've been working on. It compiles, but it still needs work.

    Code:
    If DCount("*", "tblRooms", "BuildingFK = " & Me.txtBuildingID & " And RoomsPK = " & Me.txtRoomsID > 1) Then
                      MsgBox "There are duplicates!" & vbCrLf & vbCrLf
                      'Code to remove duplicate records here
                      End If
    The MsgBox is just for troubleshooting purposes. As it is now, it displays the message box whether there are duplicates or not, so this tells me I definitely have something wrong with my code. I think I'm confused by where the ")" should go. If I put it right after Me.txtRoomsID then it never displays MsgBox, even if there are duplicates. If I put it after 1, as you see above, it always displays the message, even when there are no duplicates.

    Another thing to consider, is I'm not sure if I should be checking the table anyhow. Seems to me I should be searching through qryRecordSet and possibly strWhere, similar to what you see in the code in my first post.
    Last edited by ittechguy; 10-12-15 at 22:22.

  9. #9
    Join Date
    Aug 2015
    Posts
    20
    The ) needs to be before >1. I've found out. The reason it's not working correctly is the dcount code above searches through tblRooms. So I was right in thinking I shouldn't be checking the table. If I change it to >0 it then tells me "there are duplicates." Reason being, there is one record in tblRooms.

    That said, if using dcount I would need to search qryRecordSet and the filter variable strWhere. Similar to my dcount code in my first post which checks if there are no search results.

  10. #10
    Join Date
    Aug 2015
    Posts
    20
    I think I'm getting closer.

    I've determined that it doesn't matter if I'm searching through strWhere or through the query (qryRecordSet) because as long as the criteria is txtBuildingID and txtRoomsID.

    So then, this is my code:

    Code:
    If DCount("*", "qryRecordSet", "BuildingFK = " & Me.txtBuildingID & " And RoomsPK = " & Me.txtRoomsID > 1) Then
                      MsgBox "There are duplicates!" & vbCrLf & vbCrLf
                      'Code to remove duplicate records here
                      End If
    I've tested it by searching for a search with contains 2 records (one duplicate). If I set it to "> 1" Then it comes back with "there are duplicates!" However if I set it to ">2" it does not bring up a MsgBox.

    So then, I need the code to filter out the duplicate records. I am not sure what that would be. I think strWhere might be involved, but I'm not sure.
    Last edited by ittechguy; 10-13-15 at 01:36.

  11. #11
    Join Date
    Aug 2015
    Posts
    20
    It seems like I'm going to have to re-build my code and use a DAO recordset. Then I'll cycle through it until all but one record has been "deleted." Important thing is that the form is filtered, but values not removed from table or query.

    I don't know anything about that, so if someone could point me in the right direction, I'd appreciate it.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK before you rebuild your code try getting rid of the union query. I don't see why you'd need the UNION query for this problem in the first place its the very fact you are usign a UNION query that is creating the duplicated rows in the first place.

    TBH your current query is far to complex to give an off the cuff answer. however if you are prepared to post the db here I'll have a look at it.
    to post a db here follow the suggested steps here
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Aug 2015
    Posts
    20
    I'm attaching the database to this post. If you open form 1, search for building A, room 1100 and you will see duplicate results.

    I'm probably not going about this the correct way. Originally I tried using separate queries for each search box, however I was not able to get that to work properly.

    The purpose of the search form is to display the Building ID and the Room ID in two separate text boxes. Everything else on the form (mostly list boxes) is populated by queries based off those two list boxes. The union query was used because I needed to search through both FacilityMgr and RoomsPOC. However even if it was not a union query, there would still be duplicate records if I queried most of the database in one query. This is because one customer can own multiple rooms, one room can be owned by multiple customers, and only one piece of equipment is in a room. As you can imagine, there would be a separate row for each customer or if there was multiple equipment in the room.

    Perhaps it may be better to use different queries, but I do not know how to set that up, or if its even possible. Perhaps an even better route is, instead of the form including everything and then filtering out, I could build the form based on what is being searched. Afaik, that would still require a massive union query. But I started learning this stuff 2 months ago, so what do I know?

    But again, bare minimum, all I need to do is search by one or more of the search combo boxes and return the building ID and room ID for that search result. And if there are multiple records (suppose I searched for Michael Smith and he owned Building A, Room 1100, 1200, and 1300) then I should see multiple records in the record selector. 3, in my example.

    newDatabase.zip

  14. #14
    Join Date
    Aug 2015
    Posts
    20
    Also I asked a guy at work about this, he told me my search was all jacked up. He said I should use several smaller queries instead of one really large query.

    But then he told me that I have made work too hard for me and built an ungodly amount of tables which are not necessary, I should instead bring tables together so that there is the smallest amount of tables possible. He said that would be easier and there would be less confusion. He sent me an example of a database he built, where he used one field for a person's rank, first name, and last name.

    Not so sure I believe him. I started out with a un-normalized database. It was not working out for me, at all. I had so many problems with it it was unreal. And personally I feel like with it broken apart the way it is, its easier for me to understand what data is where. Then also I don't have to worry about duplicate data. Plus the user won't see how it was setup so that shouldn't matter...right?

  15. #15
    Join Date
    Aug 2015
    Posts
    20
    Can't wait to hear what you have to say healdem.

    I've done more research. It looks like the best (and perhaps only way) to do this is to concatenate the results of the union query so that everything is on one line w/ a unique Building ID and Room ID. I've been looking at this website here: http://www.access-programmers.co.uk/...d.php?t=208010

    It seems that concatenating is far far more complicated than I can handle. That example concatenates one field, it seems to me like I have several fields which need to be concatenated. I've been using this function here:

    Code:
    Public Function ConcatRelated(strField As String, _
        strTable As String, _
        Optional strWhere As String, _
        Optional strOrderBy As String, _
        Optional strSeparator = ", ") As Variant
    On Error GoTo Err_Handler
        'Purpose:   Generate a concatenated string of related records.
        'Return:    String variant, or Null if no matches.
        'Arguments: strField = name of field to get results from and concatenate.
        '           strTable = name of a table or query.
        '           strWhere = WHERE clause to choose the right values.
        '           strOrderBy = ORDER BY clause, for sorting the values.
        '           strSeparator = characters to use between the concatenated values.
        'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
        '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
        '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
        '           4. Returning more than 255 characters to a recordset triggers this Access bug:
        Dim rs As DAO.Recordset         'Related records
        Dim rsMV As DAO.Recordset       'Multi-valued field recordset
        Dim strSql As String            'SQL statement
        Dim strOut As String            'Output string to concatenate to.
        Dim lngLen As Long              'Length of string.
        Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
        
        'Initialize to Null
        ConcatRelated = Null
        
        'Build SQL string, and get the records.
        strSql = "SELECT " & strField & " FROM " & strTable
        If strWhere <> vbNullString Then
            strSql = strSql & " WHERE " & strWhere
        End If
        If strOrderBy <> vbNullString Then
            strSql = strSql & " ORDER BY " & strOrderBy
        End If
        Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
        'Determine if the requested field is multi-valued (Type is above 100.)
        bIsMultiValue = (rs(0).Type > 100)
        
        'Loop through the matching records
        Do While Not rs.EOF
            If bIsMultiValue Then
                'For multi-valued field, loop through the values
                Set rsMV = rs(0).Value
                Do While Not rsMV.EOF
                    If Not IsNull(rsMV(0)) Then
                        strOut = strOut & rsMV(0) & strSeparator
                    End If
                    rsMV.MoveNext
                Loop
                Set rsMV = Nothing
            ElseIf Not IsNull(rs(0)) Then
                strOut = strOut & rs(0) & strSeparator
            End If
            rs.MoveNext
        Loop
        rs.Close
        
        'Return the string without the trailing separator.
        lngLen = Len(strOut) - Len(strSeparator)
        If lngLen > 0 Then
            ConcatRelated = Left(strOut, lngLen)
        End If
    
    Exit_Handler:
        'Clean up
        Set rsMV = Nothing
        Set rs = Nothing
        Exit Function
    
    Err_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
        Resume Exit_Handler
    End Function
    I've not yet gotten it to work because I have no idea how, where, when, why it needs to be added to qryRecordSet. Will do more research. But again, it seems like that function will only concatenate one field.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •