[ms-access] How do you comment an MS-access Query?

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?

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"

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.

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.

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.

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.

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"

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!

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"

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.

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.

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.

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.

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.

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.

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.

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.

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!