List Query name and expression

[Jaye7]

I have the following script which lists the query name and it's field names.

I also want to list any expressions in the field names but am not sure how to achieve it.

i.e the expression in a field of one query is.

Desc: [PMatCatDesc] & " " & [SMatCatDesc] & " " & [SizeDesc] & " " & [ColDesc] & " " & [SysAbb]

The following script only lists the actual field name

Public Sub QueryFields()
' Read control source property of all controls in all reports.
' Note.. the debug window may not be big enough to display
' all of the data.
Dim db As Database, doc As Document, ctl As Control
Set db = CurrentDb
Dim intX As Integer
Dim SourceName1 As String
Dim qryfld As DAO.QueryDef
Dim dbCurrent As DAO.Database
Dim rstWarr As DAO.Recordset


'Add Temp Table
'FIELDS WITH SQL'S MUST BE MEMO FIELDS ARE NO DATA WILL SHOWN
    DoCmd.SetWarnings False
        DoCmd.DeleteObject acTable = acDefault, "zzzTempControlSources"
        DoCmd.RunSQL "CREATE TABLE [zzzTempControlSources] ([Primary Key Field] AUTOINCREMENT, [ID] number, [FormName] TEXT, [ControlName] Memo, [FieldDef] Memo)"
    DoCmd.SetWarnings True

Set dbCurrent = CurrentDb
Set rstWarr = dbCurrent.OpenRecordset("zzzTempControlSources") ' table name
   
'=====================================================================
Dim i, j
For i = 0 To db.QueryDefs.Count - 1
For j = 0 To db.QueryDefs(i).Fields.Count - 1

'we are adding a record to our table
   rstWarr.AddNew

'we add a value to the 1st field of the table which is named FONID
rstWarr("FormName").Value = db.QueryDefs(i).Name
rstWarr("FieldDef").Value = db.QueryDefs(i).Fields(j).Name


'we now add the data to the table
   rstWarr.Update

Next j
Next i
'=====================================================================
   
'clean up memory
   Set dbCurrent = Nothing
   Set rstWarr = Nothing
   Set db = Nothing

DoCmd.OpenTable "zzzTempControlSources", acViewNormal

End Sub

 

[Fuse3k]

The expression in Calculated fields are stored as a property to the field. you can use the Field object to get it.

Dimension a field object and a String variable to store the espression:

Dim fld as DAO.Field
Dim strFldExpression as String

After:

For j = 0 To db.QueryDefs(i).Fields.Count - 1

Add:

Set fld =db.querydefs(i).fields("CalculatedFieldName") 
strFldExpression = fld.Properties("Expression")

Hope this helps,
Brian

 

[Jaye7]

I put your code in and get the following error,
Runtime error 3265, item not found in this collection.
Is this because I should be renaming the CalculatedFieldName as something else.

Public Sub QueryFields()
' Read control source property of all controls in all reports.
' Note.. the debug window may not be big enough to display
' all of the data.
Dim db As Database, doc As Document, ctl As Control
Set db = CurrentDb
Dim intX As Integer
Dim SourceName1 As String
Dim qryfld As DAO.QueryDef
Dim dbCurrent As DAO.Database
Dim rstWarr As DAO.Recordset

Dim fld As DAO.Field
Dim strFldExpression As String

'Add Temp Table
'FIELDS WITH SQL'S MUST BE MEMO FIELDS ARE NO DATA WILL SHOWN
    DoCmd.SetWarnings False
'        DoCmd.DeleteObject acTable = acDefault, "zzzTempControlSources"
        DoCmd.RunSQL "CREATE TABLE [zzzTempControlSources] ([Primary Key Field] AUTOINCREMENT, [ID] number, [FormName] TEXT, [ControlName] Memo, [FieldDef] Memo)"
    DoCmd.SetWarnings True

Set dbCurrent = CurrentDb
Set rstWarr = dbCurrent.OpenRecordset("zzzTempControlSources") ' table name
   
'=====================================================================
Dim i, j
For i = 0 To db.QueryDefs.Count - 1
For j = 0 To db.QueryDefs(i).Fields.Count - 1

Set fld = db.QueryDefs(i).Fields("CalculatedFieldName")
strFldExpression = fld.Properties("Expression")

'we are adding a record to our table
   rstWarr.AddNew

'we add a value to the 1st field of the table which is named FONID
rstWarr("FormName").Value = db.QueryDefs(i).Name
rstWarr("FieldDef").Value = db.QueryDefs(i).Fields(j).Name
rstWarr("ControlName").Value = strFldExpression


'we now add the data to the table
   rstWarr.Update

Next j
Next i
'=====================================================================
   
'clean up memory
   Set dbCurrent = Nothing
   Set rstWarr = Nothing
   Set db = Nothing

DoCmd.OpenTable "zzzTempControlSources", acViewNormal

End Sub

 

[Fuse3k]

Yes, I'm sorry. Replace that with the name of the calculated field. There's a better way to all this, but I'm short on time. Will post tomorrow.

 

[Jaye7]

Hi Brian,

I'm not sure of the name of the calculated fields as the code searches through hundreds of fields looking for any fields that have expressions in them and then writes them to the specified table.

If you could help with the code it would be great as otherwise I have to manually check hundreds of queries looking for specific expressions in all fields, as one field might have.

Desc: [PMatCatDesc] & " " & [SMatCatDesc] & " " & [SizeDesc] & " " & [ColDesc] & " " & [SysAbb]

Another might be

PartialDescription: [LmAbb] & " " & [LMFg1] & " c1"

Etc... Etc...

 

[Fuse3k]

I went back and took a look at this, and it seems you can't get the actual expression of a Query field using .Expression (or .Properties("Expression"). That only seems to work for Tables, not Queries.

The only way I was able to actually see the expression of a field in a Query was by returning the SQL of the query (QueryDef.SQL). This output the full SQL string and includes the expression declared as a field.

Example of Expression in SQL:

SELECT Table1.Field1, Table1.Field2, Table1.Field3, [Field1] + [Field2] + [Field3] AS Expr1
FROM Table1

So, unless someone else has an idea, this is all I can offer. You could maybe parse the SQL text to retrieve field expressions. It would be a pain but it would get the job done.

 

[Jaye7]

Thanks for trying anyway.

Due to the hundreds of queries I would need a loop for each query and each field within the queries.

 

[jdraw]

All of your saved queries have their SQL available. You can loop through all saved queries, parse the SQL, populate/save whatever you want to record.

Now, if you modify the SQL via code/vba you will not have access to those mods unless you save the modified SQL.

Perhaps you could describe exactly what you want from the queries and what you want to do with the info.

 

[Jaye7]

I just want to list each query name in one field of a table and then list each field expression in the next field.

so.

List the Query name in field1 of my table and then the field expression in the next field of the table, then in the next row of the table it would be the same query name and the next field expression for that query, then when no mpore fields to list move to the next query

The following script does basically what I want except I don't want the sql of the query, I want each field's expression.

Public Sub QueryControls()

'Add Temp Table
'FIELDS WITH SQL'S MUST BE MEMO FIELDS ARE NO DATA WILL SHOWN
DoCmd.SetWarnings False
DoCmd.RunSQL "CREATE TABLE [zzzTempControlSources] ([Primary Key Field] AUTOINCREMENT, [ID] number, [FormName] TEXT, [ControlName] TEXT, [FormRecordSource] Memo, [ControlSource] Memo, [RowSource] Memo)"
DoCmd.SetWarnings True

Dim dbCurrent As DAO.Database
Dim rstWarr As DAO.Recordset

'set the Database and then the table name
Set dbCurrent = CurrentDb
Set rstWarr = dbCurrent.OpenRecordset("zzzTempControlSources") ' table name
   
Dim i As Integer
For i = 0 To CurrentDb.QueryDefs.Count - 1
  ' Debug.Print CurrentDb.QueryDefs(i).Name
   'Debug.Print CurrentDb.QueryDefs(i).SQL
    rstWarr.AddNew
        rstWarr("FormName").Value = CurrentDb.QueryDefs(i).Name
        rstWarr("FormRecordSource").Value = CurrentDb.QueryDefs(i).SQL
    rstWarr.Update
Next i
   
'clean up memory
   Set dbCurrent = Nothing
   Set rstWarr = Nothing

DoCmd.OpenTable "zzzTempControlSources", acViewNormal

End Sub

 

[vbaInet]

Like Fuse3k I also thought that the Expression property of a field would return the expression in a querydef but alas this property is only for the new Calculated Field type at table level.

Nevertheless, in a query, use the following SQL (tweaking where necessary) to return the field names and expressions in a querydef:

SELECT [Name],
       [Name1],
       [Expression]
FROM   MSysObjects
       INNER JOIN MSysQueries
               ON MSysObjects.Id = MSysQueries.ObjectId
WHERE  [Name] = "[COLOR="Blue"]Query Name[/COLOR]"
       AND [Name2] IS NULL
       AND [Expression] IS NOT NULL
       AND ( [Name1] IS NULL
             OR [Name1] IS NOT NULL );

Name1 - field name for calculated fields
Expression - expression for calculated fields or the field name of a non-calculated field.

Simply create a recordset based on the above and query by the [Name] field.