I wanted to take a query and format part of its output into English; a list of names separated by commas.
The DAO Object Library includes a completely pointless Recordset.GetRows command which returns an unwieldy multi-dimensional array. How pointless and unwieldy? Well, the supplied example to Debug.Print FirstName, LastName and Title from Northwind.mdb’s Employees table is 90 lines long. Without GetRows it would take way less than 10 lines. Amazingly, when Microsoft introduced ADO, it would retain the spectacular uselessness of this command by continuing to return a multi-dimensional array.
These functions take a query or table name or SQL command and a simple string to format the output which can contain any text with fields from the query surrounded by square brackets.
The default parameters produce a comma-delimited list with the final item delimited with an “, and” but these can be replaced with anything.
Parameters governing what is returned when there is no data in the query and what is displayed on screen as the function executes are also included and are documented in the source code.
Examples
?FromQuery(“MyTable”,”[Name] ([Age]-years-old)”)
Peter (71-years-old), Paul (71-years-old), and Mary (72-years-old)
?FromQuery(“MyTable”,”[Name] ([Age]-years-old)”, vbCrLf, vbCrLf)
Peter (71-years-old)
Paul (71-years-old)
Mary (72-years-old)
Source Code
''' <summary>
''' Converts a query to a string. Each record is formatted using Fields where field names
''' are surrounded by square brackets. Each record is separated by Delimiter.
''' </summary>
'''
''' <paramref name="Fields">A string used to format each record. Field names surrounded by square
''' brackets are replaced with the field value. For example, "Name: [Name]"
''' would produce a record that looks something like "Name: Paul J. Champion"
'''
''' <paramref name="Delimiter">Each record is separated with this string.
''' For example, if the Delimiter is ", " you would get
''' "Record 1, Record 2, Record 3, " etc
'''
''' <paramref name="FinalDelimiter">The last record is separated with this string.
''' For example,if the FinalDelimiter is ", and " you would get
''' "Record 1, Record 2, and Record 3"
'''
''' <paramref name="NoRecords">If there are no records available, this message is returned unaltered.
'''
''' <paramref name="ProgressText">If ProgressText is supplied, then a progress bar is shown in status bar.
''' If ProgressText is set to an empty string, no progress bar is shown.
''' </paramref>
Public Function FromQuery( _
QueryName As String, _
Fields As String, _
Optional Delimiter As String = ", ", _
Optional FinalDelimiter As String = ", and ", _
Optional NoRecords As String = "No data", _
Optional ProgressText As String = "Processing FromSQL query") _
As String
FromQuery = FromSQL(CurrentDb.QueryDefs(QueryName).SQL, Fields, Delimiter, FinalDelimiter, NoRecords, ProgressText)
End Function
''' <summary>
''' Converts a SQL query to a string. Each record is formatted using Fields where field names
''' are surrounded by square brackets. Each record is separated by Delimiter.
''' </summary>
'''
''' <paramref name="Fields">A string used to format each record. Field names surrounded by square
''' brackets are replaced with the field value. For example, "Name: [Name]"
''' would produce a record that looks something like "Name: Paul J. Champion"
'''
''' <paramref name="Delimiter">Each record is separated with this string.
''' For example, if the Delimiter is ", " you would get
''' "Record 1, Record 2, Record 3, " etc
'''
''' <paramref name="FinalDelimiter">The last record is separated with this string.
''' For example,if the FinalDelimiter is ", and " you would get
''' "Record 1, Record 2, and Record 3"
'''
''' <paramref name="NoRecords">If there are no records available, this message is returned unaltered.
'''
''' <paramref name="ProgressText">If ProgressText is supplied, then a progress bar is shown in status bar.
''' If ProgressText is set to an empty string, no progress bar is shown.
''' </paramref>
Public Function FromSQL( _
SQL As String, _
Fields As String, _
Optional Delimiter As String = ", ", _
Optional FinalDelimiter As String = ", and ", _
Optional NoRecords As String = "No data", _
Optional ProgressText As String = "Processing FromSQL query") _
As String
Dim Record As String
Dim rs As DAO.Recordset ' requires Reference to DAO Object Library
Set rs = CurrentDb.OpenRecordset(SQL)
If Not rs.EOF Then
' Setup progress bar if required
' Initialise boolean variable as it will be quicker for later on
Dim ShowProgress As Boolean
ShowProgress = ProgressText <> ""
If ShowProgress Then
SysCmd acSysCmdInitMeter, ProgressText, 100
rs.MoveLast ' to ensure recordset is populated
rs.MoveFirst
End If
Dim FirstField As Boolean: FirstField = True
Do While Not rs.EOF
' Reset Record
Record = Fields
' Insert fields from SQL into RecordHTML
Dim fld As DAO.Field
For Each fld In rs.Fields
Record = Replace$(Record, "[" & fld.Name & "]", Nz(fld.value), , , vbTextCompare)
Next
' Add this record to return value FromSQL
If Not FirstField Then
If rs.AbsolutePosition + 1 = rs.RecordCount Then
FromSQL = FromSQL & FinalDelimiter
Else
FromSQL = FromSQL & Delimiter
End If
End If
FromSQL = FromSQL & Record
' Update progress bar, then move to next record
If ShowProgress Then
SysCmd acSysCmdUpdateMeter, rs.PercentPosition
End If
rs.MoveNext
FirstField = False
Loop ' While Not rs.EOF
' Clear progress bar if necessary
If ShowProgress Then
SysCmd acSysCmdClearStatus
End If
Else
FromSQL = NoRecords
End If
FromSQL_exit:
On Error Resume Next
rs.Close
End Function
Classified 16+ by PEGI. The game is only suitable for persons who have reached the age of 16 or over..
Classified Violence by PEGI. Game contains depictions of violence.
Classified PG by BBFC. Parental Guidance. 
Classified 12A by BBFC. Persons under the age of 12 must be accompanied by an adult. 
Classified U by BBFC. Universal: Suitable for All. 
Batman: Arkham Asylum
Classified 15 by BBFC. Suitable only for persons of 15 years and over. 
Classified 7+ by PEGI. The game is only suitable for persons who have reached the age of 7 or over. 
