"Mark Reed" <ma*********@ntlworld.com> wrote in message news:<yU*************@newsfe1-gui.ntli.net>...
I'm guessing that from the lack of replies that this is not possible? Could
someone please confirm my suspicions.
If the subreport method doesn't work you could write a public string
function that when given the ASN opens a recordset to records with
that ASN and builds a string of all the PO's. The function can be
placed in the SQL string like:
GetPOList([ASN]) AS ListOfPOs
and then have a textbox on your Report with a ControlSource:
ListOfPOs. It's slow and not very pretty or even considered good
practice but I have tried this and it seems to work well when the
record count isn't huge.
Here's an example:
tblCustomerPurchases
CustID Long
theDate Date
Brand Text
Public Function GetBrandList(PurchaseDate As Date) As String
Dim MyDB As Database
Dim MyRS As Recordset
Dim strSQL As String
Dim strTemp As String
Dim lngI As Long
Dim lngCount As Long
GetBrandList = ""
strTemp = ""
strSQL = "SELECT Brand FROM tblCustomerPurchases WHERE theDate = #" &
Format(PurchaseDate, "m/d/yy") & "#;"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If MyRS.RecordCount > 0 Then
MyRS.MoveLast
lngCount = MyRS.RecordCount
MyRS.MoveFirst
For lngI = 1 To lngCount
If lngI <> lngCount Then
strTemp = strTemp & MyRS("Brand") & ";"
MyRS.MoveNext
Else
strTemp = strTemp & MyRS("Brand")
End If
Next lngI
End If
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
GetBrandList = strTemp
End Function
Then putting SELECT DISTINCT CustID, theDate, GetBrandList([theDate])
AS BrandList FROM tblCustomerPurchases GROUP BY CustID, theDate; as
the RecordSource of a Report allows me to drag BrandList from the
Field List into the Report Details section along with CustID and
theDate.
James A. Fortune
