Sorry about the title, but that's as close as I could come to the problem with this report. I am the patron saint of lost databases and on short notice I am taking over 20 year old application. One of the many quirks in this db is in the report design. The record source is a single row table with 60 columns. Each column is potentially another row on the report and are laid out manually. There's no sorting or grouping possible. Grouping is however being done using page breaks. The height of the report is 22", which appears to be the maximum height allowed. My task is to add several more rows to the report, but I've run out of real estate. In my ideal world I'd rework the entire report. Really, the entire db needs an overhaul, but I have about a week to turn this around.
Subtotals are all calculated using VBA in the OnFormat event. Here's a sample. All those numeric values are field names from the table, each one of which has to be run through the NulltoZero function.
Code:Me.OFee = NullToZero([O2106]) + NullToZero([O2101]) Me.NetFees = [OFee] + NullToZero([O2107]) Me.OFed = NullToZero([O1102]) + NullToZero([O1103]) + NullToZero([O1107]) + NullToZero([O1109]) + NullToZero([O1112]) + NullToZero([O1113]) + NullToZero([O1141]) + NullToZero([O1139]) Me.NetAC = NullToZero([O5107]) + NullToZero([O5116]) Me.NetFR = NullToZero([O5115]) + NullToZero([O5117]) Me.NetDADS = NullToZero([O5121]) + NullToZero([O5122]) Me.NetDAP = NullToZero([O5119]) + NullToZero([O5120]) Me.NetCS = NullToZero([O5124]) + NullToZero([O5127]) Me.NetRecovery = NullToZero([O5129]) + NullToZero([O5132]) Me.NetTrans = NullToZero([O5130]) + NullToZero([O5133]) Me.txttotalSMI = NullToZero([O1103]) + NullToZero([O1107]) + NullToZero([O1104]) Me.OFedOther = NullToZero([O1139]) + NullToZero([O1141]) + txttotalSMI Me.txtTotalMHFBGSEDCAFunds = NullToZero([O1102]) Me.txtTotalMHNetCrisisResponse = NullToZero([O5145]) + NullToZero([O5146]) EOFedOther = NullToZero([EO1139]) + NullToZero([EO1141]) + NullToZero([EO9110]) Me.OFed = Me.OFedOther + NullToZero([O1109]) + NullToZero([O1144]) + NullToZero([O1112]) + NullToZero([O1113]) + NullToZero([O1102]) + NullToZero([O1143]) Me.OEFed = Me.EOFedOther + NullToZero([EO1109]) + NullToZero([EO1112]) + NullToZero([EO1113]) Me.OUnRegional = NullToZero([O5129]) + NullToZero([O5142]) + NullToZero([O5139]) Me.AcuteCare = NullToZero([O5107]) + NullToZero([O5116]) Me.DAP = NullToZero([O5119]) + NullToZero([O5120]) DAP2014 = NullToZero([O5150]) + NullToZero([O5151]) Me.Crisis = NullToZero([O5124]) + NullToZero([O5155]) Me.Transfer = NullToZero([O5162]) + NullToZero([O5163])
Your comments are welcome.
At this point I think I'm going to just crowd everything together to create enough space for a few new rows. I'll worry about pretty later.
I just had to vent. It helped. I guarantee you I'll be back with more.