MS Access VBA – List Query Fields

The following function will return a listing of all the fields/columns that are shown in a query.

'---------------------------------------------------------------------------------------
' Procedure : listQueryFields
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a listing of all the fields (column names) of a give Query
' Copyright : The following code may be used as you please, but may not be resold, as
'             long as the header (Author, Website & Copyright) remains with the code.
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strQryName - Name of the query to list the fields of.
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2007-June-01            Initial Release
'---------------------------------------------------------------------------------------
Function listQueryFields(strQryName As String) As String
On Error GoTo listQueryFields_Error
    Dim db As DAO.Database
    Dim qryfld As DAO.QueryDef
    Dim fld As Field
   
    Set db = CurrentDb()
    Set qryfld = db.QueryDefs(strQryName)
    For Each fld In qryfld.Fields    'loop through all the fields of the Query
        Debug.Print fld.Name
    Next

Error_Handler_Exit:   
    Set qryfld = Nothing
    Set db = Nothing
    Exit Function

listQueryFields_Error:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: listQueryFields" & vbCrLf & _
    "Error Description: " & Err.Description, vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

2 responses on “MS Access VBA – List Query Fields”

  1. Kulstad

    I have inherited a database that has a number of queries that have fields defined like:

    IIf(IsNull([Table1.Field1]), 0, [Table1.Field1]) AS [New Field Name]

    Your code will only return the “New Field Name” portion (ie: the field name). Is there a way to get the underlying “IIf…..” statement from the field?

    Thanks in advance.

    1. Daniel Pineault Post author

      I’m afraid not. There is no field property available to use to get such information. You can loop through all the properties

              For Each prop In fld.Properties
            Debug.Print , prop.Name, prop.Value
        Next prop

      and see for yourself that the expression is not there. I’ve always found that Access was publically missing a lot of tools for working with SQL Statements to easily be able to parse out the different elements (SELECT,FROM, WHERE, …) and breakdown fields, …

      So, based on my knowledge, you would have to build your own custom procedure to perform such parsing of the query’s SQL Statement.