I am developing an Access 2007 "project" (.adp) as a front-end to data in a SQL Server 2005 Express database.
I will try to describe what I am trying to achieve and the problem I am having doing it.
In a particular Access Report, I allow the operators to tick (or untick) a group of 25 months, to choose which periods sales figures are to be included in a couple of columns in the report. The columns can therefore contain the combined figures from a group of selected periods.
I also allow them to tick a box that allows "all zero" rows with no valid sales figures for the selected periods to be omitted from the report.
In the Report_Open procedure I build-up a T-SQL "SELECT ... FROM .. ORDER BY ... WHERE ..." string to be used as the Report's .RecordSource parameter, which also includes a couple of "LEFT OUTER JOINs" to other "Views" stored in the SQL Server database. I also set a few of the Report's Control's .ControlSources
based on the earlier choices made by the operator using the tick boxes.
This all seems to work OK, except when I try to remove the "all-zero" rows from my report. The reason for this is that in the "SELECT" part of my Report.RecordSource string I use some aliases such as "ISNULL(OWNSHOP.Month1SlsQnty, 0) AS 'OWNMTH1QNT'. I can use the 'OWNMTH1QNT' alias name when establishing the Report's Controls' .ControlSources but the underlying Report's .RecordSource "WHERE" clause will only allow me to use the fuller "ISNULL(OWNSHOP.Month1SlsQnty, 0)" type of expression which is quite cumbersome when there are possibly 25 periods to check.
Is there a way (presumably still in the Report_Open procedure - but I will take advice to do it elsewhere), where rows from the recordset compiled for the report can be removed, before any output is generated, if some values are zero using the "alias" field names? This would also need to protect the integrity of some "totals" Controls that I have in the Report's footer section that have also been established dynamically in Report_Open (although if I am removing only "all-zero" lines from the report it should not matter if the totals are still based on the original set of records).
What would the VBA syntax look like for deleting records from such a recordset? I am hoping for an expression along the lines of "DELETE FROM ... WHERE OWNMTH1QNT = 0 AND OWNMTH3QNT = 0 ...."
I will also happily receive any suggestions for achieving my goal by any other strategy.
Many thanks in advance.