![]() |
Document Calculated Fields in Queries |
Post Reply ![]() |
Author | |||
Crystal Long ![]() Microsoft MVP ![]() Joined: 21 May 2012 Location: Colorado Status: Offline Points: 35 |
![]() ![]() ![]() ![]() ![]() Posted: 19 May 2014 at 11:51pm |
||
Document Calculated Fields in Queries
Do you have calculated fields in queries of your database? To see all your equations and fields with aliases, make a query with this SQL statement:
I put the results into Excel and formatted them -- and wrote code to do it again :) This download is a zipped up BAS file you can import into an Access database. There is one public sub called DocumentQueryCalculatedFields. When you run it, it will make a file in the CurrentProject.Path called CalculatedQueryFields.xl? (saves in whatever version you are using) with the formatted results. DOWNLOAD: uploads/84/mod_DocumentQueryCalculatedFields_Crystal_140521_BAS.zip example documentation: ![]() The first database I ran this on had nearly 500 equations in queries! This documentation was a huge help to figure out where fields were being calculated that did not show up in the Field List generated by my Analyzer*. One report I looked at had a RecordSource based on 3 queries ... by the time I was done tracing the nested queries down to tables, I had opened about 20 more queries. * http://www.AccessMVP.com/strive4peace/Analyzer.htm It is also nice to see the long equations that would be better in a global function ~ and where they are used. For example, if you are reporting a date range that may have both beginning and ending dates filled, neither, or one, this will require several IIF statements if you want to customize each set of conditions. The formula gets long and hard to trace. By putting logic into a public function that everything calls, it is easy to reference and change. Here is an example of a function that returns a string representing a date range.
Can you imagine how long the equation was that did this? ... and it was in several queries ... Naturally, the code is also longer because of the optional psDescription parameter, which gets added to the result if specified, error handling, and comments (nice!). After running this, I also found equations that were different but getting the same result ... just written at different times so different logic was used. ___ A bonus on the download is that you can see how to write the results of an SQL statement to Excel and do some nice formatting. Field names are stacked into an array, which is used to write the labels across the top. CopyFromRecordset is used to write the data. Data filters are added, whatever value is in the first column is bolded each time it changes (along with a top border stretching across the data), margins and Page Header are set, columns are best-fit, columns wider than 60 (you can change this of course) are wrapped and width is set to 60, and the top row and left column are frozen. Late Binding is used, so no libraries need to be referenced. Constant names for early binding are in the comments. here is the code in the BAS download:
Warm Regards, Crystal Remote Training and Programming connect to me, let's build it together * (: have an awesome day :) * |
|||
Warm Regards,
Crystal Microsoft MVP Remote Training & Programming Let's Connect and Build Together http://www.AccessMVP.com/strive4peace http://YouTube.com/LearnAccessByCrystal ~have an awesome day ~ |
|||
![]() |
|||
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
||
![]() |
Post Reply ![]() |
|
Tweet
|
Forum Jump | Forum Permissions ![]() You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |