Software

Office challenge: Why isn't your Access query returning all of the expected records?

This week, test your skills in Access queries and learn the solution to last week's challenge on Word styles.
Access queries speak a specific and literal language. It's easy to misstate your intentions. For example, suppose you query your 150 customers for those living in a specific ZIP code and the query returns 45 records. A second query returning all customers who do not live in that ZIP code returns 100—that's 145! Where are the other 5 customers? They should be in one or the other query, right? Not necessarily. Last week we asked… How do you change formatting for comments? The answer is a bit elusive for two reasons: You use the Balloon Text style to alter comment formatting, and the Balloon Text style may not be readily available. You change the Balloon Text style the same way you would any style -- just knowing to use that style is what trips up most people. To change the Balloon Text style (or any style really), complete the following steps:

  1. Display the Styles And Formatting task pane by choosing Styles And Formatting from the Format menu or clicking the Styles And Formatting button on the Formatting toolbar.
  2. Choose All Styles from the Show drop-down list (at the bottom of the task pane). Depending on the document, you could expose dozens of styles.
  3. If Balloon Text is in the list (most likely, it will not be) right-click it and choose Modify.
  4. In the Format Style dialog box, choose Font from the Format button at the bottom of the dialog box, make the changes you want, and click OK twice.
  5. If Balloon Text isn't available, choose Custom from the Show drop-down list.
  6. In the resulting Format Settings dialog box, check Balloon Text in the Styles To Be Visible list and click OK.

If Balloon Text still isn't visible in the Pick Formatting To Apply list, you have one last shot at it. Make sure the document isn't protected. To do so, choose Unprotect Document from the Tools menu and try again.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

14 comments
gbradshaw
gbradshaw

I would suspect that the zip code field was blank

tballky
tballky

Agreed, null is the most likely answer although selecting strings can be tricky. Assuming a 5 character zip, the following will return all records to allow further record selection with regard to both nulls and the missing leading zero(s) issue: Right(nz("00000"+[zip]),5)

jswed
jswed

There are five records whose ZIP code field is Null.

richslab
richslab

Comparisons for equality are fairly straightforward. Since a null value returns false in any comparison it gives you the result you expect. Inequality is a bit trickier since intuitively you expect value null to be true but that's not how it's treated. To correctly do inequality comparisons when some values may be null you need to do something like this: Nz([zip], '') [search_zip]

kathy.wagner
kathy.wagner

If the zip field was not using entry validation, and left open for 9 digit zip codes, a space in front of a five digit zip code would cause the issue as well.

RU7
RU7

Here's my view of the best way to fix this (since the question states zip codes, I am assuming 5 or 10 character strings). zip = "12345*" not(zip = "12345*")

nonsy
nonsy

Could be for several reasons, depending on how the query is structured. The main one that springs to mind is a null zipcode - the info doesn't exist - this could cause the problem. In australia, northern territory postcodes (equivalent of zip codes) start with a zero - and they can be alternately treated as a string or a number "0750" 750. I'm not sure if there is a US zipcode equivalent.

miscris
miscris

In this particular case invovling a zip code that could be 5 or 9 digits, the easiest fix would be to enter the 5 digit zip code followed by an asterisk. This way the query will pick up either zip code.

amasa
amasa

This is such a general statement being used as a question, that the possibilities could seem endless. However, I immediately thought of a few things. What if some zip codes were left blank, what if some used the full 9 digits, etc. These simple differences can cause problems. A troubleshooting technique I use for the type situation you mentioned would be to sort the query for all other zip codes by the zip code (either print it or use a second query to have a second window to view side-by-side). Then run the same query, but listing all zip codes and sorted by zip code. That way I can scan the two results, and I typically find the few records which weren't returned in either query result. Then I can look at the data and judge why neither query returned those specific records.

RU7
RU7

But I don't think it would cause this problem. Even if there were missing or extra digits, records would fall into the equal to or the not equal to query results.

ian
ian

The most likely answer is that some contain null. Access treats nulls differently to other values in that they are "neither equal to a specified comparator nor not equal to it - a bit like Schroedinger's cat.

JimTheGeordie
JimTheGeordie

All the possible answers seem to have been covered. However, I would like to offer the comment that in a well-organised database, this sort of thing should not occur. Significant identifiers such as the postcode should be tabulated and selected from lists - usually displayed in combo boxes and such-like. The current buzzword is Metadata.

youzer
youzer

Thanks for posting my answer ;)