Ms Access VBA: Return the Formula behind a Query Field

Question

I have a Sub Procedure that will create a table based on an existing query with 2 text fields (FieldName and SourceName). the value of FieldName will be the name of each field in the existing query, and the value of SourceName will be the name of the table or Query that the field comes from. See the code below. What I am looking to do is to also include the formulas for calculated fields in a 3rd field called FieldFormula. Does anyone know if this is possible? Thank you!

Option Compare Database

Public Sub MapQuery()

 Dim strQueryName As String
 Dim rst As DAO.Recordset
 Dim fld As Field
 Dim strSource As String
 Dim strField As String
 Dim strValue As String
 Dim strSQL1 As String
 Dim strSQL2 As String
 Dim booExists As Boolean


strQueryName = InputBox("Please enter the name of the query that you are looking to map")

   Set rst = CurrentDb.OpenRecordset(strQueryName)
    intLen = Len(strQueryName)
    strnewtablename = Right(strQueryName, intLen - 4)
On Error GoTo error1
    booExists = IsObject(CurrentDb.TableDefs(strnewtablename & " Definitions"))
    DoCmd.DeleteObject acTable, strnewtablename & " Definitions"
continue:
    strSQL1 = "CREATE TABLE [" & strnewtablename & " Definitions]" & " (FieldName CHAR, SourceName CHAR);"
    DoCmd.RunSQL (strSQL1)
    DoCmd.SetWarnings False
    For Each fld In rst.Fields

    strField = fld.Name
    strSource = fld.SourceTable


Debug.Print strValue

    strSQL2 = "INSERT INTO [" & strnewtablename & " Definitions]" & "(FieldName, SourceName) VALUES(""" & strField & """, """ & strSource & """);"

    DoCmd.RunSQL (strSQL2)
    Next fld

error1:
    If Err.Number = 3265 Then
        Resume continue
    Else
    MsgBox Err.Description

    End If

    DoCmd.SetWarnings True
    Exit Sub

    DoCmd.SetWarnings True
End Sub

Interesting question. I would not look in Recordset.Fields, but in QueryDef.Fields.Item(x). But a cursory look in the Watch window didn't show it. Maybe the formulas exist only in the QueryDef.SQL property, and are parsed for display in the query designer grid. — Andre
if it's anywhere, you should be able to get it from fld.properties("expression"), but it's not returning anything for me. See here — Beth

June7 June7 · Accepted Answer · 2017-10-30T21:19:54

Pulling the expression from a table Calculated field is simple. I did a quick test in the VBA immediate window:

CurrentDb.TableDefs("Teams").Fields("Test").Properties("Expression")

And that returns the expression string. I expect your code will have to do conditional statement that checks if the field is a Calculated type.

However, if you want to pull expression of a calculated field in query, that is very different. There is no "Expression" property as there really is not a field entity. I would say @Andre explained it with 'exist only in the QueryDef.SQL property' as part of the statement string.

Ms Access VBA: Return the Formula behind a Query Field

1 Answers