Jim Lawrence
accessd at shaw.ca
Wed Dec 5 12:32:42 CST 2007
That sounds like it should work Rocky. Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin at
Beach Access Software
Sent: Wednesday, December 05, 2007 6:23 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Report Filter Problem
Jim:
That's how I do it normally. However, in this case each group of records
needs to be evaluated at the time the report is being generated and in one
case only records meeting the criterion are to be printed, and in the other
case all records in the group need to be printed if only one meets the
criterion.
So I'm thinking of two solutions - one is to do a little post-processing on
the temp table that the report is based on; add a yes/no field, set it based
on the user option on the calling form, and then retrieve only the 'yes'
records. The other option is to look at each record in the detail format
event and use the following:
If DIFFERENCE < .01 Then
Me.MoveLayout = False
Me.NextRecord = True
Me.PrintSection = False
End If
The second solution would only take a couple of minutes to implement and
test. It might take a little longer to generate the report but the amount
of data is not great.
So as usual even when I don't get a 'Do it this way." response from the
list, just talking about it generates a solution. It's like psychotherapy -
the 'talking cure'. We could call it 'cyber-therapy' and market it. But
you have to really want to find a solution.
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Wednesday, December 05, 2007 3:48 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Report Filter Problem
Hi Rocky:
Do you call the form that sets the filter value from OPEN_REPORT procedure?
I traditionally:
1. Call the Form from the On_Open event.
2. From the Form the Record Source is then set.
(ie. Me.RecordSource = "qryDifference")
I never play with filters as I ran into some issues a few years ago and this
method does not seem to have the same problems.
HTH
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin at
Beach Access Software
Sent: Tuesday, December 04, 2007 9:12 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Report Filter Problem
Jim:
I kept the same query for the report - actually just printing pretty much
straight off of a temp table. But on the calling form there's an option
frame which says to 1) print all records, or 2) only those where the
DIFFERENCE >= .01. This eliminates the zero difference records and the ones
which are different by less than a cent.
The report is printed product by product. So I've added a grouping level on
Product.
So for a group of records for one product, if they've selected 2) in the
option frame, the user now wants all records to print, even if for most of
the records the DIFFERENCE is < .01.
So I thought that just removing the filter in that case, and replacing it
when done with the group would do it. But, for some inexplicable reason,
the report just keeps running itself - like it's calling itself.
Weird behavior I've never seen before but it must have something to do with
removing and turning off the filter in the group header, and then in the
replacing the filter and turning it on again in the group footer. Because
when I run it with the all records option selected on the calling form, it
runs fine.
Any ideas?
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Tuesday, December 04, 2007 4:28 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Report Filter Problem
Hi Rocky:
Can I assume the query if actually
Select * from table where difference >= .01 changed to select * from table
where difference > 1
Is this query actually run against a combined query used in the report? I
have found that sometimes a combined query does not work as planned. When
you run this query directly against the report query/table does it work? Try
putting brackets around the filter like:
"(difference > 1.0)" and see if that produces any change.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin at
Beach Access Software
Sent: Tuesday, December 04, 2007 10:10 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Report Filter Problem
Dear List:
I have a report grouped on Product. In the detail there are two number -
standard and actual cost. There's an option on the calling form to print
all records or only records where the difference is > .01. In the report's
Open event, then, I set the filter:
If Forms!frmPricePaidVersusContractReport!fraPrint = 1 Then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = "Difference >=.01"
Me.FilterOn = True
End If
All's well so far.
Then the client said to change the report so that if the option to print
only if DIFFERENCE > .01 and ANY ONE record in the product qualified, then
print all records for that product.
So in the format event of the header of the Product I do a little code to
see if ANY ONE record qualifies and if so I turn the filter off:
ShowDetail:
Me.Filter = ""
Me.FilterOn = False
and then in the Format event of the Product Footer I conditionally turn the
filter back on again with same code I used in the open event.
Problem is that it goes into a loop which I traced down to the report being
run over and over again. I put a break point in the Open event, and it just
keeps running the report again and again.
What is going wrong here - or better yet - what's the solution?
MTIA
Rocky
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.13/1169 - Release Date: 12/3/2007
10:56 PM
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.14/1171 - Release Date: 12/4/2007
7:31 PM
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com