IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, January 2004
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.)
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.
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.
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.
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Calculated fields being misinterpreted'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...