Form Report Summary - Loop and Concatenate Results?

[misscrf]

I have a report menu that gives users the results in a datasheet view for any “report” that they want to run. This allows users to filter based on many listboxes and choose the fields that they want to show in the summary. The code I use to run the “report summary” is here:

Private Sub cmdSummary_Click()
 Dim Mysql As String
 Dim strCriteria As String
 Dim strFields As String
 Dim strQuery As String
 Dim lbo As ListBox
 Dim itm
 Dim i As Long
 strQuery = "qryOrderByLocationFields"
 Set lbo = Me.lstFields
 If Me.lstFields.ListIndex = -1 Then
 For i = 0 To Me.lstFields.ListCount - 1
 Me.lstFields.Selected(i) = True
 Next
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 Else
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 End If
 strCriteria = "1=1 "
 strCriteria = strCriteria & _
 BuildIn(Me.LstOrderStatus, "[Order Status]", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstState, "State", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstCity, "City", "'")
 Mysql = "SELECT  " & strFields & " FROM  qryOrderByLocationReport inner JOIN qrydtCurrentCity ON (qrydtCurrentCity.PKOrderID = qryOrderByLocationReport.[Order ID]) AND (qrydtCurrentCity.MaxOfdtEffectiveDate = qryOrderByLocationReport.[City Effective Date]) Where " & strCriteria & " Group By " & strFields
 CurrentDb.QueryDefs(strQuery).SQL = Mysql
 Me.frmSubStatusCityQry.SourceObject = "QUERY." & strQuery
 Me.frmSubStatusCityQry.Visible = True
End Sub

This also relies on this function:

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, strDelim As String) As String
    'send in a list box control object
    'strFieldName is the name of the field in the report's record source
    'strDelim is the delimiter for numbers use "" for text """" and dates "#"
    Dim strIn As String
    Dim varItem As Variant
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " AND " & strFieldName & " In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        'remove the last ", " and add the ")"
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn
End Function

(note – I did not create this, I found it)

So the way that all works is that it is always only pulling the most recent city that an order is in. The way this works is that an order can be placed in 1 city and then be transferred to another. When it is placed or transferred there is an effective date for the date that order is now in that city.

The users had requested initially that they would only ever want to see the most recent city on the report summaries that they would run.

Now the scope has changed. They want to be able to specifically request to see the orders where there has been a transfer from 1 city to another. (i.e. more than 1 location record exists for that order).

So this is my thought. I will put a checkbox on the status location report form. It currently has 4 listboxes –

LstOrderStatus – listing the possible statuses so the user can choose 1 or more to filter on
lstState – listing the possible states so the user can choose 1 or more to filter on
lstCity – listing the possible cities so the user can choose 1 or more to filter on
lstFields – listing of all possible fields to select, group by and display in the summary report.

If I add a checkbox to this form, it would act as ckTransfer. When checked (=1) then I would need to add an IF statement to the above code to provide a different sql statement. This new one would omit the join that ties to the query looking for the max effective date for an order’s city location. Instead all would get pulled.

Here is my hard part. I need to make a summary field that would concatenate all order locations into 1 field.

So if someone checks that box, they would choose the summary fields Order Name, Order Status, and “Transfer Locations”. Transfer locations would be a calculated field that would be (I have no idea how to do this) a concatenation of all the locations and effective dates the order had, in date order. For example, if an order had 3 location records, the “Transfer Locations” field would look like this:

SanFransisco 1/1/10, Chicago 2/15/10, New York City 3/22/10

So here is the short question : How do I do that?

I have another question after I get this accomplished, but 1 step at a time. If I need to explain anything else, please let me know. I appreciate any help!!!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

Have a look here
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

Thanks! I will take a look and let you know how it goes.

misscrf

It is never too late to become what you could have been ~ George Eliot