Some report records not printing

[azimuth79]

Hello all,

I am new to the boards, and this is my first of what will most likely be many questions.

I have developed a fairly substantial database used to record inspection data of sanitary sewer manholes. I am in the final stages of printing all of the reports for our client, however, I've run across a small problem.

There is a field one of my tables that I am sorting on called district, which is a numerical field numbered 1-6. I am sorting based on a district, and then printing all of the corresponding reports. However, I am noticing that some reports are simply being left out. For instance, I've also sorted in ascending order by manhole number. If I have manholes numbered J-1, J-2 and J-3, on occasion, J-2 will not print, and it will not show up in the print preview window either.

I'm wondering if any of the access guru's out there could shed some light on this for me. I've made sure there are no spaces in my table in front of the district, and I've exhausted all other means of solving this problem on my own, so I turn to you all for guidance. Any help would be greatly appreciated.

Kind Regards,

J. Miller

 

[Moniker]

Never count on an ORDER BY for things like that. If your sure that a J-2 exists for each each district, then use a GROUP BY query as the base for the report's control source. If the J-2 doesn't exist, then that's the case. Otherwise, strange things (an extra space like "J-2 " or " J-2" or other oddities) will cause all sorts of super-nice person problems. Using a a GROUP BY query will show you these problems quickly. (A GROUP BY query is done by clicking the Sigma symbol in the toolbar in query design mode.)

The better way around this is to normalize the data at the earliest point possible. If your manhole covers are imported from "on the street" reports (as it were), then import them using a TRIM function. If you're counting on others to import them, then an UPDATE query that performs a TRIM on all the string fields will remove the leading and trailing spaces. Ideally, the textbox that captures these values is already being written to your recordset with a TRIM, as in:

ManholeNumber = Trim([ManholeFieldName])

The earlier you catch these things, the better. Also note that if you need leading or trailing spaces for some formatting reasons, the LTRIM will remove all the blank spaces from the start of a string, and RTRIM will remove all the blank spaces from the end of a string. It all looks like this:

TRIM(" test ") = "test"
LTRIM(" test ") = "test "
RTRIM(" test ") = " test"

And note, I didn't make one off-color comment even after saying "manhole" several times.

~Moniker