troubleshooting Question

Can I delete records from a recordset before printing an Access 2007 report?

Avatar of colinasad
colinasadFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessMicrosoft SQL Server
7 Comments1 Solution357 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
colinasad

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros