Every now and again, we stumble across a new “gotcha” in our favorite software. Within one week, I was confronted (perplexed, vexed, antagonized) with these two little beauties. So, in the hope of helping you avoid these two annoyances in the future, I present them now for your reading pleasure.

Double, triple, quadruple join lines!
One of the types of assignments I enjoy is what I call “shepherding.” In this relationship, my client has developed a database and wants “one-on-one” training/support to further along the effort. There are times when I have to get in there and write some code, but I do it with the user right there explaining and documenting as I go along.

My client had some reports that weren’t returning the expected results. The report was based on a query, so we opened the query directly and did some exploration. I realized that what she wanted was an Outer Join so that all of the records from Table A would display, regardless of whether there was a matching record in Table B.

So we double-clicked on the existing join line to see the Join Properties dialog box. When it was set as an Equal Join, as shown in Figure A, the join line looked like the one shown in Figure B. After I changed it to an outer join by selecting option 2, as shown in Figure C, the join line changed to include an arrowhead, as shown in Figure D.

Figure A
With this setting in place, my client’s report didn’t return the expected results.

Figure B
With the equal join active, the join line looks like this.

Figure C
In this case, the second option provides the outer join we need to create the report.

Figure D
When we activated the outer join, the join line changed to show the arrowhead.

We ran the query, and we still didn’t get what we wanted. I double-checked the join line, reran the query, and it still was not correct. Hmmm. In these situations, it is sometimes best to start over, so I clicked on the join line to select it, pressed the [Delete] key on the keyboard and—WAIT A STINKIN’ MINUTE! The join line was still there! So I did it again, and the join line was STILL THERE! Somehow, my client was able to create three join lines, some equal, some outer, between the same fields in the two tables. Once we got it down to a single join line, everything ran just fine.

To duplicate this in Access 97, simply drag the same field between the same two tables as many times as you want. Each time you drag and drop, you will create an additional (and probably unwanted) join line. Be afraid; be very afraid.

About Access 2000

In Access 2000, I was not able to replicate this using the drag-and-drop joining method. However, I was able to create the same situation by dragging the secondary table into the query grid a second time, selecting its properties, and then changing its alias (Access automatically creates an alias for a table if the same name is already in the query) back to the original table name. To its credit, Access 2000 warns me that I can’t use the same table name in the “FROM clause” when I try to run, or open, the query. Even so, when I open the query in design view, I see only one apparent join line between the two tables. It’s not until I click on the “SQL” view that I see the duplication of table names.

Compilation errors in modules affecting queries
While visiting a different client later that week, I was in clean-up mode in some code I had recently written. I realized there were some inconsistent naming conventions, so I was renaming and moving code around within the module. I finished that task and was working on a report—just some layout fine-tuning.

In the course of working on this newest report, I wanted to reference how I did something in an older report. This report has been around since “Day One” and hasn’t been touched since about “Day Three” (how many times have you heard that one!). So I ran the report, and I got these error messages about not being able to run such functions as Year( ), Month( ), and Now( ), which I have in the underlying query for this report.

I popped up the Debug window and these functions worked well there, so I figured that it must be something in that query. I also had a function that I had written, so I tried taking that out. No luck—the query still didn’t work.

At that point, I began to backtrack in my mind as to what I had changed recently. Ah ha—it must have been when I had the old Electrolux out cleaning up that code.

Even though none of the routines in that module were referenced in this query, I figured I would do a little exploration. I couldn’t see anything, but I thought I would recompile just to be sure. “Look at that,” I exclaimed across two rows of cubicles. In my quest to clean up my code, I had “stranded” an additional “End Function” at the bottom of the module. Once I deleted this offending line, my code compiled, my query ran, and my report ran.

So here’s the moral of the story. Even if you think that there might not be a link between one part of Access and another, your software may have a different opinion.

Peter Nelson is the principal of NewMarket Technologies in Saratoga Springs, NY. NewMarket provides customized software training and applications development to its clients. While the company’s client base includes manufacturing, government, and small business, NewMarket has a specific focus on brokerage and insurance markets.