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
An outer join involving a literal value in the join field behaves as an inner join in Access. You are less likely to experience this flaw than most of those discussed in this section. It is included as it does illustrate a weakness in the query engine (JET) in Access.
An inner join returns only records where there is a match in both tables. For example, an inner join between Customers and Orders returns only the Customers who have Orders. An outer join lets you return all the Customers, along with any orders they have. To change the join type in the Query Design window in Access, double-click the line joining the two tables. Access presents a dialog offering 3 options:
The first option is the inner join. The other two are the left join and right join - the two directions of the outer join.
If the outer join exists on a literal value in a query instead of a field in a table, the outer join does not return all the records that it should. In fact, it omits the Nulls from the outside of the join, and returns only the records where the joined fields from both tables are equal. This behaviour would be correct for an inner join, but is wrong for an outer join.
To demonstrate the flaw with AccessFlaws.zip, open the query named BadSelectQuery. This query is based on another query named BadSelectBase and a table named BadSelect.
The BadSelectBase query contains a calculated field that returns the literal value 1. The BadSelectQuery uses a RIGHT JOIN between that query and the table. The resultant query ought to contain all three records from the table, along with any matches from the stacked query. Instead, it yields only one record from the table.
For the boffins, the SQL statement of the query reads:
SELECT BadSelectBase.MyLiteral, BadSelect.MyText FROM BadSelectBase RIGHT JOIN BadSelect ON BadSelectBase.MyLiteral = BadSelect.MyNumber;
This is a clear example of Access missing records it ought to return.
There are other examples that result from different implementations of the SQL standard:
|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 'Records missed by SELECT query (bug)'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...