Link to home
Start Free TrialLog in
Avatar of colinasad
colinasadFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

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.
Avatar of colinasad
Flag of United Kingdom of Great Britain and Northern Ireland image


I have just finished posting my question and have just had an idea of my own.
(It's amazing how things come to you when you have to think through a problem in order to ask a question.)
I will investigate making the Report's "Detail.Visible" value False if all my "sales" Control text boxes are zero. As I mentioned in my original question, it won't matter if the omitted records are included in the totals at the end of the report because their values are all zero anyway.
I will check that out. In the meantime any other suggestions are still welcom.
Avatar of Jeffrey Coachman
...then something like this might help on the Detail_Format event:

If me.Sales=0 then
End if
Avatar of colinasad
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

You can apply a filter to a recordset that returns a recordset a subset of the initial recordset.  Simple to code and very flexible.  See examples at:

PS: I belive the line in Microsoft's example that reads:

  Set rest = dbs.OpenRecordset(_

should be

  Set rst = dbs.OpenRecordset(_


You can do it this way or do the same thing in a query.
I would go with the query approach because then you could use the same query for a form, or just to see the results directly.

For example, this query will only display records where none of the Val fields is Zero:
(In other words, if any Valn value is zero, the record will not display)

SELECT ID, FName, Val1, Val2, Val3, IIf(([Val1]=0) Or ([Val2]=0) Or ([Val3]=0),-1,0) AS Hide
FROM YourTable
WHERE (((IIf(([Val1]=0) Or ([Val2]=0) Or ([Val3]=0),-1,0))=False));

Now you can use this query as the source for the report (or a form or simply run the query to see the results directly)

I an sure you can see where I am going with this...
So you can adapt this basic concept to your distinct situation


Thanks for your suggestions, but I think I am going to stick with my own solution.

The problem is that the Access Report itself consists of only a few columns. However, the Form that the operator sees before the Report is launched offers many (25) periods that can be ticked for inclusion in those columns.

In the Report_Open procedure I build up (Report's and) the columns' underlying "DataSource" strings based on the periods that have been ticked in preceding Form.

The other option I offer the operator in the preceding Form is to "Ignore all-zero" lines. (This means ignoring lines that have ALL zero values rather than ANY zero values.)

This seems easier to do in the Report's "Detail_Format" procdure, using the Report's columns control names, rather than making my "RecordSoutce"/"DataSource" SQL Wuery strings even more complicated.

Again, many thanks for your time and suggestions.
Please see my previous comment for why I chose my own solution to this question.