Melissa:
You've changed the name of the strPageNumbers variable to PAGE. It should
have been left as it was, but that should not cause an error.
You've also changed the argument's name from strStreetName to Label2.
Better would be strLable2 to distinguish it from the column name. Again it
should not cause an error, however.
Also, in the query, you've used PAGE as the name for the computed column
returned by the function. Normally you'd use a name which differs from an
existing column name, e.g. PageNumList as in my example, but in this case it
again should not cause an error.
I suspect that where you've gone wrong is in building the query, as you
mention an 'expression'. So I'm not sure just where you've created this
'expression' (in a column in design view?). What you should do is:
1. Open the query designer, but don't add any table to it.
2. Switch to SQL view.
3. The query window will contain SELECT; In place of this paste in:
SELECT DISTINCT [Label2],
GetPageNumbers([Label2]) AS [PageNumList]
FROM [StrSumm];
4. Switch to Datasheet View. If all is well it should open and return the
correct rows. You can switch to design view if you wish to see how the same
query would be built in design view. Save the query.
The function should work as you've written it, but I'd suggest you change it
to:
Public Function GetPageNumbers(strLabel2 As String) As String
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strPageNumbers As String
strSQL = "SELECT DISTINCT [Label2], [PAGE] " & _
"FROM [StrSumm] " & _
"WHERE [Label2] = """ & strLabel2 & """ " & _
"ORDER BY [PAGE]"
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly
' loop through rows and build list of page numbers
Do While Not .EOF
strPageNumbers = strPageNumbers & ", " & _
.Fields("PAGE")
.MoveNext
Loop
.Close
' remove leading comma and space
strPageNumbers = Mid$( strPageNumbers, 3)
End With
GetPageNumbers = strPageNumbers
End Function
If you still have problems open the debug window (aka the Immediate window)
by pressing Ctrl+G. In the debug window enter:
? GetPageNumbers("Main St")
using a real street name exactly as it exists in rows in your table. This
will test the function as once you press the Enter key it should list the
page numbers for Main Street on the next line. If you get a compilation
error its probably because you don't have a reference to the Microsoft
ActiveX Data Objects Library, so select Tools | References on the VBA menu
bar and scroll down to the library and check it.
It you get a runtime error then make a note of the error message. There
should be a 'debug' option in the error dialogue, so select this and see
which line in the function it goes to. Post back with the information.
Ken Sheridan
Stafford, England
Melissa said:
Okay, so I am working this out...I do not use modules that often so thanks
for your patience!
My field for the name is Label2, my field for the page number is PAGE, the
table name is StrSumm
So..I changed the code to:
Public Function GetPageNumbers(Label2 As String) As String
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim PAGE As String
strSQL = "SELECT DISTINCT [Label2], [PAGE] " & _
"FROM [StrSumm] " & _
"WHERE [Label2] = """ & Label2 & """ " & _
"ORDER BY [PAGE]"
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly
' loop through rows and build list of page numbers
Do While Not .EOF
PAGE = PAGE & ", " & _
.Fields("PAGE")
.MoveNext
Loop
.Close
' remove leading comma and space
PAGE= Mid$(PAGE, 3)
End With
GetPageNumbers = PAGE
End Function
I have created the module and saved it as mdlStreetPages. I created a new
query and tried to build and expression with:
SELECT DISTINCT [Label2],
GetPageNumbers([Label2]) AS [PAGE]
FROM [StrSumm];
It gave me a syntax error so I am sure I'm doing something wrong. Maybe I'm
doing it in the wrong place. Any help is really appreciated! I really want
to learn how to do this.. Thanks!
Ken Sheridan said:
Melissa:
You can use a VBA function to return a list of all the page numbers for each
street name, so add the following function to a standard module, changing the
table name to your own:
Public Function GetPageNumbers(strStreetName As String) As String
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strPageNumbers As String
strSQL = "SELECT DISTINCT [Street Name], [Page Num] " & _
"FROM [YourTable] " & _
"WHERE [Street Name] = """ & strStreetName & """ " & _
"ORDER BY [Page Num]"
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly
' loop through rows and build list of page numbers
Do While Not .EOF
strPageNumbers = strPageNumbers & ", " & _
.Fields("Page Num")
.MoveNext
Loop
.Close
' remove leading comma and space
strPageNumbers = Mid$(strPageNumbers, 3)
End With
GetPageNumbers = strPageNumbers
End Function
If you are adding it to a new module save the module with a different name
to that of the function, e.g. mdlStreetStuff.
Base the report on a query which returns distinct values of the street names
and calls the function, passing the street name into it as its argument, to
return a list of page numbers, e.g.
SELECT DISTINCT [Street Name],
GetPageNumbers([StreetName]) AS [PageNumList]
FROM [YourTable];
Sort the report by street name and bind controls to the Street Name and
PageNumList fields.
Ken Sheridan
Stafford, England
:
I have a question that I hope is easy. I am doing a report. The fields I am
displaying are Street name and page number. In my table I have several
street names that are the same that could have multiple page numbers. I am
putting them in alphabetical order, but I would like the street name to
appear only once, and all of the page numbers that are associated with that
name to be separated by commas on one line. Is there a way to do this?
Example,
Street name Page num
Main St. 1
Main St. 1
Main St. 5
result wanted:
Main St. 1,5
Thanks you in advance,
Melissa
