Text in a Report Header

[cinnie]

hello all

My report, rptInv is based on qryInv. Three of the fields in this query are
pkID, CatID (integer) and PartName (string). Like this:

pkID CatID PartName
156 25 WR_left
157 25 WR_right
158 25 WR_left
159 36 Flange_17a

I print a separate report for each CatID. In the report's header, I'd like
to show a text box with a list of the DISTINCT PartNames on that report. For
example, the report for CatID 25 would have a text box showing :

WR_left, WR_Right

I can't seem to figure out how to do this properly. Any suggestions?

thank you

 

[Ofer Cohen]

I don't know of a way except of using some code

In the Report Header create a text box
In the Report Header OnPrint event write the code
==============================================
Dim MyDB As DAO.Database, MyRec As DAO.Recordset, MyStr As String

Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset(Me.RecordSource)
While Not MyRec.EOF
MyStr = MyStr & MyRec![PartName] & ", "
MyRec.MoveNext
Wend
Me.[TextBoxName] = Left(MyStr, Len(MyStr) - 2)
==========================================

 

[Duane Hookom]

You can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Your
control source might be
=Concatenate("SELECT DISTINCT PartName FROM qryInv WHERE CatID = "
& CatID)