Document Query Field Calculations

[glasgow.27]

Hello everyone,

Is it possible to get to the calculation for a field in an Access Query via VBA? I know that I could parse the SQL string, but I was thinking that there had to be an easier, more direct way to get the the information.

To clarify, take the following example:

• A table contains two columns ([Column A] and [Column B])
• A query has a calculated field (Product: [Column A] * [Column B])

Is there a way to get to "[Column A] * [Column B]"? I've tried using the Expression Property of the field (QueryDef.Field.Property("Expression").value), but that just returns a null string. Any help would be appreciated. Thanks.

[jonh]

Have a look in MSysQueries.
You can get the ObjectId from MSysObjects.

[glasgow.27]

Have a look in MSysQueries.
You can get the ObjectId from MSysObjects.

Thank you for your reply jonh. Your suggestion sent me in the right direction. I ultimately didn't use VBA, but instead solved my need through a query. For reference, I used the SQL code below to list the expressions by query.

SELECT DISTINCT MSysObjects.Name, MSysQueries.Name1, MSysQueries.Expression, IIf([Flags]=0,"Select",IIf([Flags]=16,"Crosstab",IIf([Flags]=32,"Delete",IIf([Flags]=48,"Update",IIf([flags]=64,"Append",IIf([flags]=128,"Union",[Flags])))))) AS TypeFROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
WHERE (((MSysQueries.Name1) Is Not Null) AND ((MSysQueries.Flag)=0))
ORDER BY MSysQueries.Name1;