I get a certain amount of mail every month that patiently tries to educate me. For instance, in the “Access Answers” column in our December 2000 issue, I proposed an answer to a reader’s problem. The problem was to provide a count of True/False choices entered in the database. I used two queries and the IIF function to handle the problem. I also suggested at the time that I’d be hearing from readers with better solutions than I had proposed. Within two days of the issue coming out, Russell Sinclair e-mailed me with a better solution:
You can count the number of times a True/False value appears without using Access functions. The easiest way is to take advantage of how Access stores Boolean values: 0 for False, -1 for True (and Nulls never allowed). This code does the job:
SELECT City, -(SUM(Survey)) As InSurveyTotal, _ SUM((Survey + 1)) As NoSurveyTotal, _ COUNT(City) As CityTotal FROM Customers GROUP BY City
This SQL statement will convert the True/False values in the Survey field to a numeric sum. Totaling the True/-1 fields is straightforward, though the result is a negative number. I use a negative sign to inverse the result to get a positive. To sum the False values, I add 1 to each value. This causes the Trues to be converted to 0 and the Falses to be converted to 1. Summing the result of the Survey field plus 1 then gives the total of the False entries. I've been using this trick for years in Access.
Avoiding embarrassments like this is the very reason that Mike Gunderloy gets to handle these questions in our Smart Access eXTRA e-mail newsletter. Not only do I get to avoid having better answers shown to me, but the e-mail newsletter lets us get a response back to readers faster (and it’s free).
We also got mail from Ken Getz, who pointed out that while Stuart Kinnear mentioned the Developer’s Handbook’s resizing code in his December 2000 article, “Everything About Resizing Controls,” he didn’t describe the full version of the utility. You can download the fully functional, compiled version of the resizer from the Developer’s Handbook site at http:// www.developershandbook.com/. Ken is justifiably proud of the resizing utility, which comes with the full source code as part of the Developer’s Handbook (and Ken’s personal support).
Keith Bombard’s menu utility, which he described in “User-Driven Menus” in our January 2001 issue, was one of our more popular downloads. A number of readers wanted to convert Keith’s application to Access 2000 but were having problems with security, probably due to the owner for some of the objects not being the Admin user. Keith created new files, using Admin as the owner, and also rebuilt a new system MDW file to go with the files. The new version has been posted to our Web site for the January issue.
As you might have gathered, I think e-mail is the greatest part of the Internet. I live in a little town with a population of 7,500, quite a distance off the beaten path. I moved here to take a job as head of the IT department with one of the town’s major employers (the other is a salt mine—really). I realized that, for a number of reasons, I wouldn’t be retiring from this company. I’ve never stayed at any company for more than five years, for instance. Also, this company had five levels of management between the head of the company and the people doing the actual work. In a company with fewer than 1,000 employees, it seemed to me that it was only a matter of time before one or more levels would be removed— probably mine. Since I wanted to stay in this town, I needed a new job that didn’t require me to live in any particular place.
E-mail lets me do exactly that. In terms of Microsoft’s vision of Web services, you’d be hard pressed to find a service nearly as useful as e-mail. To make that point, Keith Bombard is back this month with an article that demonstrates how you can integrate mail into your Access application. Keith makes the case that the e-mail functionality that he’s added is almost as important as the actual functionality of the application.
Take advantage of e-mail—don’t hesitate to get in touch.