How do I create a list?

[micereza]

I would like to create report that creates an index that pulls from two different fields in one table. For example:

(Topic) (Page Numbers where the topic can be found)
Housing 1,34,56,77,79,90

Currently the report allows me to do this:

Housing 1
Housing 34
Housing 56
Housing 77

It does not allow me to list the page numbers if they are all coming from one field.

 

[scriverb]

This should work for you but it is not done in the report. Requires a new table, a Union Query, and some VBA code before you run the report. You then use the resulting table as the Record Source for the report.

I created a test table called tblYourTableName. It has two fields Housing (Long Integer) and DayCare (Long Integer). I put in a bunch of numeric data. I put numbers in one and not the other and visa versa as well as in both for testing purposes.

New Table: Named tblTopicAndPages Fields: RecCounter, AutoNumber; Topic, Text(20); PageToFindTopic, Text(100)

UNION query: Create a blank UNION query and paste in the following SQL:
SELECT "Housing" as Topic, tblYourTableName.Housing as Pages
FROM tblYourTableName
WHERE (((tblYourTableName.Housing)>0))
ORDER BY tblYourTableName.Housing

UNION SELECT "DayCare" as Topic, tblYourTableName.DayCare as Pages
FROM tblYourTableName
WHERE (((tblYourTableName.DayCare)>0));

Feel free to change the name of the table references in this query to the name of your table. Also, the field names must match your fields. But, the column names and query structure must stay the same.

Now to the VBA code. Paste this code into a button on a form in front of the code to open and run your report.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyRS As DAO.Recordset
Dim vPagesStr As String
Dim vTopic As String
Set db = CurrentDb
Set rs = db.OpenRecordset("qryUNIONTopicPages", dbOpenDynaset)
Set MyRS = db.OpenRecordset("tblTopicAndPages", dbOpenDynaset)
rs.MoveFirst
Do
vTopic = rs("Topic&quot
vPagesStr = ""
Do
vPagesStr = vPagesStr & IIf(vPagesStr = "", CStr(rs("Pages&quot), "," & CStr(rs("Pages&quot))
rs.MoveNext
If rs.EOF Then GoTo Skip_LoopUntil
Loop Until vTopic <> rs(&quot;Topic&quot
Skip_LoopUntil:
MyRS.AddNew
MyRS(&quot;Topic&quot = vTopic
MyRS(&quot;PageToFindTopic&quot = vPagesStr
MyRS.Update
Loop Until rs.EOF
rs.Close
MyRS.Close
db.Close

Your statement to open the report would follow. The table tblTopicAndPages will now have in it one record for each topic and the page references listing numerically left to right and seperated by commas. Use this table as the RecordSource for your report. No sorting necessary. Bob Scriver