X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> VBA Logic To Find Queries With Formulas, Access 2013    
post Aug 9 2018, 10:24 AM

Posts: 197
Joined: 9-September 11

Hi, I've been asked to find how many formulas exist in a database I'm working on. Not sure how to do that but for starters, I'm focusing on queries. Planning to build a procedure to traverse all the columns in all the queries, looking for formulas. My problem - how to tell if a column contains a formula ?

So far I think the approach would be, if column type is some kind of numeric, and (fld.SourceTable = vbNullString) and (fld.SourceField = vbNullString), then it may be a formula (upon further inspection).

Being the optimist that I am smile.gif I'm hoping there is a more straighforward approach, such as an IsFormula attribute. Not seeing it tho, and not holding my breath. Maybe I'm not an optimist after all. frown.gif

If anybody has a clue how to proceed, I'd appreciate it.

Thank you.

ps at the moment I'm just trying to get a foothold by identifying what fld properties I might leverage, as below

Debug.Print "fld name: " & fld.Name & ", Number: " & IsNumeric(fld.Type) & ", Empty Sourcetable: " & (fld.SourceTable = vbNullString) & ", EmptySourcefld: " & (fld.SourceField = vbNullString)
fld name: Amt, Number: True, Empty Sourcetable: True, EmptySourcefld: True

This post has been edited by ChiliDog: Aug 9 2018, 10:29 AM
Go to the top of the page
post Aug 9 2018, 10:33 AM

Access Wiki and Forums Moderator
Posts: 74,477
Joined: 19-June 07
From: SunnySandyEggo


Just thinking out loud... I wonder if there's an IsUpdatable property for query columns. I imagine formula columns are not updatable, correct?

Go to the top of the page
post Aug 9 2018, 10:37 AM

UtterAccess VIP
Posts: 9,373
Joined: 25-October 10
From: Gulf South USA

Wow, that's quite a request! In over 25 years of creating Access databases for a wide variety of clients, I have never heard that request.

And what about formulae in Forms and Reports (in the body as well as in queries included as record sources) plus Modules (within Forms and Reports as well as standalone)?

I suppose you could export the SQL from all saved queries and analyze those by looking at the fields with As components, but I don't have an good response for you. Could you tell us what this result will accomplish? Just curious ...

Go to the top of the page
post Aug 9 2018, 10:42 AM

Posts: 1,889
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


For clarity, can you describe what you/requestor mean by "formulas"?
A few examples might help with context.
Go to the top of the page
post Aug 9 2018, 10:51 AM

Posts: 197
Joined: 9-September 11

Thanks for the responses.

DBGuy yes there's property qdf.Fields("Amt").Properties("DataUpdatable") which I guess you're suggesting I could use to support my logic "if field is numeric and sourcetable empty and sourcefield empty and dataupdatable=false" then it must be a formula

RJD yes reports, forms etc would be the next hurdle but gotta start somewhere smile.gif
>> Could you tell us what this result will accomplish?
Nope, I just get the ask. not sure what they're after.
Thanks for the pointer on the SQL. I was poking around at field level, "geez where's the stupid field definition" when I saw your post...duh...its at the querydef level.

Thanks again, I'll plug away and see what I can come up with.

ps orange999, a calc'd field in a query...more broadly, any field which does not exist in the tables I guess, example:

_Rates])))*-1) AS Amt

This post has been edited by ChiliDog: Aug 9 2018, 10:54 AM
Go to the top of the page
post Aug 9 2018, 11:22 AM

Posts: 1,889
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I recall a thread by Glenn that had some ideas that may help with part of your project.
Go to the top of the page
post Aug 9 2018, 01:57 PM

Posts: 2,176
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. I'm following right along with theDBguy's Logic in Post# 2 where he suggests testing the Updateability of a Query Column to determine whether or not a Query Column is actually updateable.
  2. I created a simple Demo that will loop thru all Non-Temporary Queries and all Fields contained within those Queries.
  3. If the Field is NOT Updateable, it prints the Query Name, Field Name, and False to indicated that it is not Updateable.
  4. I imagine that this would simply be the starting point, other factors can be entered directly into the Logic.
  5. I executed the Code against the Northwind 2007 Sample Database and I have included both the Code and partial Output from it.
  6. Code Definition:
    Dim qdf As DAO.QueryDef
    Dim fld As DAO.Field

    Debug.Print "Query Name"; Tab(42); "Field Name"; Tab(69); "Updateable?"
    Debug.Print String(80, "-")

    For Each qdf In CurrentDb.QueryDefs
      If Left$(qdf.Name, 1) <> "~" Then
        For Each fld In qdf.Fields
          If Not fld.DataUpdatable Then
            Debug.Print qdf.Name; Tab(42); "[" & fld.Name & "]"; Tab(69); "False"
          End If
      End If

    Debug.Print String(80, "-")
  7. Sample OUTPUT (partial only):
    Query Name                               Field Name                 Updateable?
    Customers Extended                       [File As]                  False
    Customers Extended                       [Contact Name]             False
    Employees Extended                       [File As]                  False
    Employees Extended                       [Employee Name]            False
    Inventory                                [Product ID]               False
    Inventory                                [Product Name]             False
    Inventory                                [Product Code]             False
    Inventory                                [Qty Purchased]            False
    Inventory                                [Qty Sold]                 False
    Inventory                                [Qty On Hold]              False
    Inventory                                [Qty On Hand]              False
    Inventory                                [Qty Available]            False
    Inventory                                [Qty On Order]             False
    Inventory                                [Qty On Back Order]        False
    Inventory                                [Reorder Level]            False
    Inventory                                [Target Level]             False
    Inventory                                [Qty Below Target Level]   False
    Inventory                                [Current Level]            False
    Inventory                                [Qty To Reorder]           False
    Inventory on Hold                        [Product ID]               False
    Inventory on Hold                        [Quantity On Hold]         False
    Inventory on Order                       [Product ID]               False
    Inventory on Order                       [Quantity On Order]        False
    Inventory Purchased                      [Product ID]               False
    Inventory Purchased                      [Quantity Purchased]       False
    Inventory Sold                           [Product ID]               False
    Inventory Sold                           [Quantity Sold]            False
    Invoice Data                             [Salesperson]              False
    Invoice Data                             [ExtendedPrice]            False
    Order Details Extended                   [Extended Price]           False
    Order Price Totals                       [OrderID]                  False
    Order Price Totals                       [Price Total]              False
    Order Subtotals                          [Order ID]                 False
    Order Subtotals                          [Subtotal]                 False
    Order Summary                            [Order ID]                 False
    Order Summary                            [Employee ID]              False
    Order Summary                            [Customer ID]              False
    Order Summary                            [Order Date]               False
    Order Summary                            [Shipped Date]             False
    Order Summary                            [Sub Total]                False
    Order Summary                            [Shipping Fee]             False
    Order Summary                            [Taxes]                    False
    Order Summary                            [Order Total]              False
    Order Summary                            [Ship Name]                False
    Order Summary                            [Ship Address]             False
    Order Summary                            [Paid Date]                False
    Order Summary                            [Status]                   False
    Product Category Sales by Date           [Order Date]               False
    Product Category Sales by Date           [SumOfQuantity]            False
    Product Category Sales by Date           [Category]                 False
    Product Orders                           [Transaction]              False
    Product Purchases                        [Transaction]              False
    Product Sales by Category                [Amount]                   False
    Product Sales Qty by Employee and Date   [Order Date]               False
    Product Sales Qty by Employee and Date   [Employee Name]            False
    Product Sales Qty by Employee and Date   [SumOfQuantity]            False
    Product Sales Qty by Employee and Date   [Product Name]             False
    Product Sales Total by Date              [Product Name]             False
    Product Sales Total by Date              [Order Date]               False
    Product Sales Total by Date              [Amount]                   False
  8. As far as the Code's accuracy and legitimacy, I'll leave that for you to determine.
Go to the top of the page
post Aug 10 2018, 10:37 AM

Posts: 197
Joined: 9-September 11

Thank you ADezii for that, and to all who replied. Just wanted to update in case anyone is following, and especially if anyone has any thoughts, because maybe I'm going about this the wrong way - I do that sometimes smile.gif

I built a couple of procedures which loop thru the queries as shown in previous posts. I used split() to split the SQL (after stripping off everything at/after FROM keyword) which gives me a variant array of the list of fields in the select statement that is good enough to work with. Then I threw in a boatload of exceptions, for example ignore TRANSFORM queries, UPDATE queries, ignore fields which aren't numeric, and so on.

So eventually the loop ends up with a field which is probably a formula. I throw the queryname, fieldname, and 'formula' from the variant array into a table for further review. I use this to refine the exceptions mentioned above, enventually reaching a point where there's a reasonable set of fields that are formulas. Querynames are morphed here intentionally. Not exact by any means but best I can come up with. Below is an example.

As mentioned, this is just a starting point and does not cover formulas in modules, reports, etc.

Any feedback welcome. This may be the end of the analysis effort depending on what client says.

And thank you again everyone.

ps How do you get the neat columnar content Adezii has for Output section ? Can't figure it out. I've uploaded a screenshot as a lame proxy frown.gif

This post has been edited by ChiliDog: Aug 10 2018, 11:08 AM
Attached File(s)
Attached File  Capture.PNG ( 51.99K )Number of downloads: 5
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th February 2019 - 10:03 PM