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, firstname.lastname@example.org
You wish to mail two different messages - one to your WA friends and another to those in other states. You create two queries: one where the Criteria line under State reads WA , and a second query where the Criteria line reads Not "WA" . You could think this would take care of all cases. It doesn't!
The problem is the way Access and other true relational databases handle Nulls. If the entry under State has been left completely blank, the record will not show up in either of the above queries. You must specifically ask Access to check for Nulls, by using a Criteria such as Is Null Or Not "WA". Whenever you enter criteria for a query, think about the possibilities of Nulls.
In some cases, you will want to prevent Nulls occurring. In Access 2 or later, open the table in Design View, click on the field, and in the properties at the bottom of the screen, set Required to "Yes".
The same "Gotcha!" occurs when you have two tables joined on a field that can contain a Null. For example, a Customer table might be joined to an Invoice table on a CustomerID field. To cover "Cash Sales", you allow invoices to be entered with a blank CustomerID.
Now you create a query with both the Customer and Invoice tables, as the basis for a report showing all sales. If you are observant, you notice that your Cash Sales are missing, so the report has incorrect totals!
The solution is very simple. In the Query Design grid, double-click the line that joins the two queries, and Access will pop up a dialog box asking what type of JOIN you want. Select the appropriate OUTER JOIN, and all the "Cash Sales" will reappear in your query and in the report.
For more information about handling nulls, see: Common errors with Null.
|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 'The query lost my records!'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...