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, December 2005
You can ask Access to return just the unique values from a field, by setting Unique Values to Yes in the query properties. Access adds the DISTINCT predicate to the SQL View of the query, so the example pictured becomes:
SELECT DISTINCT Customers.Fax FROM Customers ORDER BY Customers.Fax;
If some rows are Null, the query naturally shows one Null row.
However, if the field is indexed "No Duplicates", DISTINCT does not work correctly. Access fails to de-duplicate the Nulls, and returns a row for every Null record in the table.
The number of records returned by the query is therefore inconsistent, depending how the field is indexed. You cannot trust DISTINCT to de-duplicate correctly, if any of the fields might contain a Null.
This problem exists in all versions of Access.
The query now returns some twenty-six Null rows. The same query gives 25 more records than it did at step 4.
|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 'DISTINCT query handles Nulls inconsistently'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...