querydef with formula

[Guest]

I have written some visual basic scripts to analyze a complex Access 2000
database here at work. I have generated a list of tables and queries and
their corresponding fields.
I am now trying to show the source fields/tables for the fields in my
queries. This works fine except when a field uses a formula. Is there a way
to show the formula that generates a field? I can get the field Name from
the querydef, but don't know where to find the formula portion of the field.

For example, the field in the query is
MonthYear: Format([Date],"mm/yyyy")

CurrentDb.QueryDefs(X).Fields(Y).Name gives me MonthYear. I can't find the
Format([DATE],"mm/yyyy") stored in the querydef.

 

[Tim Ferguson]

CurrentDb.QueryDefs(X).Fields(Y).Name gives me MonthYear. I can't
find the Format([DATE],"mm/yyyy") stored in the querydef.

You'll need to parse out the SELECT part of the SQL of the query. You can
get at the text using QueryDefs("X").SQL but after that it's up to you and
a bunch of Instr() and Mid() functions...

Best of luck!


Tim F