VBA to write Access Query to Excel, Document Calculated Fields in queries, free tool
Channel: LearnAccessByCrystal
Date: 2014-05-29

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:

SELECT mQ.Name1 AS FieldName
     , mo.Name AS QryName
     , mQ.Expression  
 FROM MSysObjects AS mo 
     INNER JOIN MSysQueries AS mQ 
         ON mo.Id = mQ.ObjectId  
 WHERE ( (mQ.Name1 Is Not Null) 
     AND (left(mo.Name,1) Not IN ("~","{")) 
     AND (mQ.Expression Is Not Null) 
     AND (mQ.Attribute=6) )  
 ORDER BY mQ.Name1
     , mo.Name;

*****

This demo show a free tool you can download to put the results of the SQL statement into Excel and format them. In addition to getting great documentation for your database, see how to use VBA (Visual Basic for Applications) to: 

- Stack field names into an array to write the labels across the top of a worksheet
- Write data from Access to Excel 
- Use CopyFromRecordset (which I originally learned about from NateO)
- Add data Filters in Excel
- Format Excel ranges with borders and shading
- Bold face text in Excel cells
- Page Setup including Margins, Page Headers, and Orientation
- Best-fit all columns
- Freeze Panes -- top row and left column
- Create an Excel workbook with the formatted SQL results that is easy to share!

~~~
Links

Download free tool to Document Access Query Calculated Fields from RogersAccessLibrary. 

Document Calculated Fields in Queries
http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html

To learn about Access, read my free Access Basics book
http://www.AccessMVP.com/strive4peace

... and watch the videos here:
 
Learn Access Playlist on YouTube
http://www.youtube.com/view_play_list?p=1B2705CCB40CA4CA

*** Original Music  ***

excerpts from "Slade", by JD Live (Jerome Dixon, Houston)

JD's Websites 

    * http://myspace.com/jdlivepro
    * http://www.youtube.com/user/jdlivepro
______________________________
 
The VBA source code is included in the free download. If you are a developer and expand it, or are interested in being on a development team, please let me know so your great stuff can be shared, thanks  ~

happy learning! ... and documenting :) 

______________________
Intro and End: Microsoft Office PowerPoint.
Screen-shots: Techsmith SnagIt.
Screen capture and production: Techsmith Camtasia
Audio edited with GoldWave
~~~~~~~~
Capture and Production Settings:
Video: H.264, 24-bit color, 30 fps, 1280x720
Audio: PCM, 44,100 kHz, 16-bit, 172 kb/sec
~~~~~~~~
Render Format: QuickTime MOV
~~~~~~~~

* let's connect *
Do you need help? You know your business. I know how to teach, write programs, and develop applications. If you have a budget and want control of your project, let's connect and build it together!

visit MsAccessGurus and contact me today!
http://msaccessgurus.com 

I look forward to hearing from you ~

warm regards,
crystal

Remote Programming & Training -- connect to me, let's built it together

~ have an awesome day ~