Microsoft Access Tips for Serious Users

Provided by allenbrowne.com, January 2004

Calculated fields misinterpreted

Unlike fields in a table, there is no way to specify the data type for calculated fields. If your data sorts incorrectly, or the wrong records are returned, Access is probably treating your calculated numeric or date field as text.

Typecast calculated fields to avoid these errors.

Calculated fields are widely used in normalized databases, and this problem is ubiquitous. There are postings every day in the newsgroups that trace back to this issue. (If this is a new area for you, see calculated fields.)

In Queries

This example calculates when payment is due - 30 days from the order date, or today if the order date is blank:

SELECT OrderID, Nz(DateAdd("d",30,[OrderDate]),Date()) AS PaymentDue
FROM Orders;

Test it by opening the Northwind sample database, creating a new query, and pasting the statement into SQL View (View menu). You will notice that Access left-aligns the PaymentDue field. That is a visual clue that it understands the calculated field as text. Sort or filter on this field, and your results will be wrong.

To specify the data type of a calculated field, wrap the calculation in CDate(), CLng(), CDbl(), CCur(), etc.:

SELECT OrderID, CDate(Nz(DateAdd("d",30,[OrderDate]),Date())) AS PaymentDue
FROM Orders;

These conversion functions cannot handle Null. Use Nz() inside the conversion.

Hint: Use CVDate(). While this function is a vestige from Access Basic, it is incredibly useful for calculated date fields. It allows you to output Nulls and still have Access recognise the data type correctly.

In Forms and Reports

For calculated controls on a form or report, all you need do is set the Format property of the control.

In general, you should think of the Format property as affecting how the data is presented to the user, not how it is stored in the database. Nevertheless, if you set the Format property of a calculated control to a numeric or date type, Access will recognize and process the data correctly.

This works for unbound controls as well: Set the Format to "Short Date", and Access will not accept an invalid date. Set the Format to "General Number", and the user cannot enter a non-numeric value.

In VBA Code

Whenever you work with Variants, there is a danger that the data type can be misunderstood. Variants do have a subtype, so the problem is less prevalent than with calculated fields, but it is still good practice to explicitly typecast, especially when working with fields or mixed data types.

For more information, see Working with Variables.