In this article I want to touch upon Field Captions. Why you should Love them and Hate them!
What Is A Caption?
The first thing we need to do is understand what the caption property is exactly:
In plain English, a caption is a ‘legible’ label that will be used to label the field in queries, form and reports instead on the actual field name. So you can use best practices, abbreviation and the likes when naming a field, and use the caption to provide the full version (or whatever you’d like).
For instance, you might have:
a Field named ‘FirstName’ with a Caption of ‘First Name’
Or
a Field named ‘Qty’ with a Caption of ‘Order Quantity’
Or
a Field named ‘EmpNo’ with a Caption of ‘Employee Badge Number’
…
The PROs
By defining a caption property when you work in Queries, Forms and Reports, the caption will automatically be used for Column heading, label for associated controls, … thus simplifying your development.
The CONs!
Their is one notable CON when using Field Captions! That is the fact that you cannot alias a captioned field in a query. The alias is simply ignored and the caption used instead.
So say we have a table with a field named ‘FirstName’ with a caption of ‘First Name’ and we build a query of:
SELECT FirstName AS [Contact First Name] FROM SomeTable;
You’d expect to get a column returned to you named ‘Contact First Name’, but you wont! Instead, you will get a column entitled ‘First Name’
A Workaround To Aliasing Captioned Fields
There is a workaround. We need to build an expression instead of just using the field directly. Once we have an expression, then we can Alias it without issue.
Thus we could rework the above to say:
SELECT Nz([FirstName], "") & "" AS [Contact First Name] FROM SomeTable;
and now, we should get returned a column entitled ‘Contact First Name’.
If we were dealing with a numeric field then we would simply multiply the value by 1 to create and expression. For instance:
SELECT UnitPrice * 1 AS [Item Unit Price] FROM SomeTable;
and we should get returned a column entitled ‘Item Unit Price’ instead of any assigned caption.
Avoiding Captions Altogether?
Personally, I tend to avoid Captions altogether.
This avoids the issue completely, then I don’t have to implement such workarounds everywhere and the tradeoff of having to set labels, from time to time, is a minimal burden at the end of the day (and can even be automated), but you can decide which approach you prefer as you are the master of your project!
Let’s Get Things Fixed!
I’ve created a suggestion that this issue should be fixed, so feel feel to add your support by up-voting the suggestion:
Here is a new suggestion on the subject. Sadly I’ve lost any of the original votes/supports, so please, once again, I ask you to support the suggestion and upvote it!
I do not caption my fields beause I find it easier to reference the correct name when I am writing SQL statements. I can just look at a query i’m working on and get the correct field name.
Speaking of creating forms: What I truly wish i’d have, is a means of pulling in new fields into a form and having them popup with the correct size and shape based on the underlying data. I’ve created my own subs to do this, but they are still piecemeal at best.
What is more vexing, and this is true also for the Description property (for both fields and tables, in fact), is that this property is not documented.
In the case of the field’s Description property, at least (I haven’t tried this yet for the Caption property or the table’s Description property), references to it fail and iterating DAO.Field.Properties does not reveal that any Description property exists.
“Field.Description” clearly is a property of some object, although it isn’t clear which. If it is a DAO.Field object property, it also isn’t clear why it is neither exposed for reading nor documented.