Avatar of colinasad
colinasad
Flag 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.
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
colinasad

8/22/2022 - Mon
colinasad

ASKER
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.
Jeffrey Coachman

...then something like this might help on the Detail_Format event:

If me.Sales=0 then
    me.section(0).visible=False
else
    me.section(0).visible=True
End if
ASKER CERTIFIED SOLUTION
colinasad

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
AielloJ

colinasad:

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:

http://msdn.microsoft.com/en-us/library/ff837300.aspx

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

  Set rest = dbs.OpenRecordset(_

should be

  Set rst = dbs.OpenRecordset(_

Regards,

AielloJ
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jeffrey Coachman

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

;-)

JeffCoachman
Database162.mdb
colinasad

ASKER
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.
colinasad

ASKER
Please see my previous comment for why I chose my own solution to this question.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.