How do you comment an MS-access Query?

Question

How does one add a comment to an MS Access Query, to provide a description of what it does?

Once added, how can one retrieve such comments programmatically?

Parma

Also applies to ADODB JET Queries! – Fiacre

Answer 1

NOTE: Confirmed with Access 2003, don't know about earlier versions.

For a query in an MDB you can right-click in the query designer (anywhere in the empty space where the tables are), select Properties from the context menu, and enter text in the Description property.

You're limited to 256 characters, but it's better than nothing.

You can get at the description programatically with something like this:

Dim db As Database
Dim qry As QueryDef

Set db = Application.CurrentDb
Set qry = db.QueryDefs("myQuery")

Debug.Print qry.Properties("Description")

Motch

Doesn't seem to me like a code comment. However, I can understand the proposed answer. It's a way, but hardly the answer anyone would expect when reading this. – Uncertain

O-M-G! It's 2021 and Access (and Office VBA dev in general) still SUX!? Essentially 2-3 DECADE-old tech!? Even simple things like making the open "Windows" window resizable (so one can actually distinguish between multiple open windows in the stingy ~263-pixel wide window whose width can easily be used up by the db name it prefixes each window name with), which, btw, would not break existing code, have not been done!? They need to replace the VBA IDE with (or offer as an option) a stripped-down version of the latest VS IDE and the VBA language with (or offer as an option) VB.NET! – Rebate

Answer 2

I decided to add a condition to the Where Clause that always evaluates true but allows the coder to find your comment.

Select
   ...
From
   ...
Where
   ....
   And "Comment: FYI, Access doesn't support normal comments!"<>""

The last line always evaluates to true so it doesn't affect the data returned but allows you to leave a comment for the next guy.

Sadowski

It's still not a comment per-se, but better than nothing, and definitely good for what the main purpose of comments are for: helping you (/the next person) to remember what's going on. – Lamartine

This should be the selected answer. This is a great workaround. The clugieness is the fault of Access. – Alcoholic

You don't need the <> "" comparison. Just WHERE ... AND "-- whatever" – Entrench

Five years later, I've found a variation of this method that allows the comment to be adjacent to the relevant code instead of separated off in a WHERE clause. See my new answer added below. – Alcoholic

I said above that the clugieness of this method is Access's fault. I think I was wrong about that. It's SQL's fault. – Alcoholic

Answer 3

It is not possible to add comments to 'normal' Access queries, that is, a QueryDef in an mdb, which is why a number of people recommend storing the sql for queries in a table.

Oca

Is that a comment in the usual sense? To me, it is a custom property, not a comment. – Oca

I would agree with @Remou. While it's a way of "commenting" on the whole query, it's not really what you'd usually want, i.e., a way to comment the parts of the query, and format the layout (both of which are impossible). – Syllabub

Answer 4

I know this question is very old, but I would like to add a few points, strangely omitted:

1. you can right-click the query in the container, and click properties, and fill that with your description. The text you input that way is also accessible in design view, in the Descrption property
2. Each field can be documented as well. Just make sure the properties window is open, then click the query field you want to document, and fill the Description (just above the too little known Format property)

It's a bit sad that no product (I know of) documents these query fields descriptions and expressions.

Watchtower

You're aware that the DESCRIPTION field you're filling out in your step 2 is going to be displayed in the status bar if you bind a form control to the field? You can see this by setting the description property of a query field and then viewing it in datasheet view. When you put the focus on the field you added a description to, the text appears in the status bar. This is the purpose of this field, actually, the same as it is in table design. – Syllabub

Answer 5

You can add a comment to an MSAccess query as follows: Create a dummy field in the query. Not elegant but is self-documentating and contained in the query, which makes cheking it into source code control alot more feasible! Jere's an example. Go into SQL view and add the dummy field (you can do from design view too):

SELECT  "2011-01-21;JTR;Added FIELD02;;2011-01-20;JTR;Added qryHISTORY;;" as qryHISTORY,  ...rest of query here...

Run the query:

qryHISTORY                           FIELD01 FIELD02 ...
2011-01-21;JTR;Added FIELD02;;2011-01-20;JTR;Added qryHISTORY;;"  0000001  ABCDEF ...

Note the use of ";" as field delimiter in qryHISTORY field, and ";;" as an end of comment, and use of ISO date format and intials, as well as comment. Have tested this with up to 646 characters in the qryHISTORY field.

Cleistogamy

Could this be edited for clarity, please? I'm having a hard time reading/understanding it. Some more formatting, spacing, etc., in the code might help. – Xmas

I'm not sure most installations would want the history being returned with every query call, & prefer @Dan's answer below – Lamartine

Answer 6

The first answer mentioned how to get the description property programatically. If you're going to bother with program anyway, since the comments in the query are so kludgy, instead of trying to put the comments in the query, maybe it's better to put them in a program and use the program to make all your queries

Dim dbs As DAO.Database
Dim qry As DAO.QueryDef

Set dbs = CurrentDb
'put your comments wherever in your program makes the most sense
dbs.QueryDefs("qryName").SQL = "SELECT whatever.fields FROM whatever_table;"
DoCmd.OpenQuery "qryname"

Amund

Answer 7

If you have a query with a lot of criteria, it can be tricky to remember what each one does. I add a text field into the original table - call it "comments" or "documentation". Then I include it in the query with a comment for each criteria.

Comments need to be written like like this so that all relevant rows are returned. Unfortunately, as I'm a new poster, I can't add a screenshot!

So here goes without

Field:   | Comment              |ContractStatus     | ProblemDealtWith | ...... |

Table:   | ElecContracts        |ElecContracts      | ElecContracts    | ...... |

Sort:  

Show:  

Criteria | <> "all problems are | "objection" Or |

         | picked up with this  | "rejected" Or  |

         | criteria" OR Is Null | "rolled"       |

         | OR ""

<> tells the query to choose rows that are not equal to the text you entered, otherwise it will only pick up fields that have text equal to your comment i.e. none!

" " enclose your comment in quotes

OR Is Null OR "" tells your query to include any rows that have no data in the comments field , otherwise it won't return anything!

Allynallys

Answer 8

I've been using the method in the answer by @Dan above for five years, and now realized there's a similar way that allows a comment adjacent to the relevant code, not separated away in a WHERE clause:

A comment can be added in the field code using iif(). For example, if the field code is:

A / B

then it can be commented like so:

iif("Comment: B is never 0." = "", "", A / B)

or

iif(false, "Comment: B is never 0.", A / B)

or

iif(0, "Comment: B is never 0.", A / B)

Like Dan's solution, it's kludgy, but that's SQL's fault.

Alcoholic

Answer 9

if you are trying to add a general note to the overall object (query or table etc..)

Access 2016 go to navigation pane, highlight object, right click, select object / table properties, add a note in the description window i.e. inventory "table last last updated 05/31/17"

Iguanodon

Answer 10

In the query design:

• add a column
• enter your comment (in quotes) in the field
• uncheck Show
• sort in assending.

Note:

If you don't sort, the field will be removed by access. So, make sure you've unchecked show and sorted the column.

Feinstein