VBA - What am I doing wrong?

[Programmer - wannaB]

I have 5 sub-reports that I am trying to set filters, based on a date field
in the first sub-report. This code ALMOST works, but only if I use “On Error
Resume Nextâ€, and then even thought I have stepped through it and watched it
go through the ELSE, and it works while stepping. When I run it with DEBUG
OFF it doesn’t turn the filters off. The last 4 columns still show data for
2006 – 2003, where they should all five show data for 2008 – 2004.
Is there anyone out there who could please help me to write this code
correctly??? Thank you.

 

[Programmer - wannaB]

So Sorry I forgot to post the Code...

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
On Error Resume Next
If Year(Me.rFP10!FHDate) < Year(Now()) Then
Me.rFP11.Report.FilterOn = True
Me.rFP11.Report.Filter = "year(FHDate)=(Year(Now()) -2)"
Me.rFP12.Report.FilterOn = True
Me.rFP12.Report.Filter = "year(FHDate)=(Year(Now()) -3)"
Me.rFP13.Report.FilterOn = True
Me.rFP13.Report.Filter = "year(FHDate)=(Year(Now()) -4)"
Me.rFP14.Report.FilterOn = True
Me.rFP14.Report.Filter = "year(FHDate)=(Year(Now()) -5)"
Else
Me.rFP11.Report.FilterOn = False
Me.rFP12.Report.FilterOn = False
Me.rFP13.Report.FilterOn = False
Me.rFP14.Report.FilterOn = False
End If
End Sub
======================

 

[BruceM]

Try using this syntax for the filter string:
"Year(FHDate) = " & Year(Now)-2

I don't know if this will solve the problem, but it is something I noticed.

You could try error handling that identifies the error:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

On Error GoTo ProcErr

{Your code here}

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in DetailPrint"
Resume ProcExit

End Sub

I'm not sure what you meant by "the last four columns".

 

[Programmer - wannaB]

Thanks I tried the error catch and it shows me what I already knew, but
hadn't psoted, the error is>>
Run-time error '2101':
The setting you entered isn't valid for this property.
[Continue] [End] [Debug] [Help]
and it happens on the first line that attempts to turn the Filter on, So it
is not an issue with the actual filter, that seems to work and pull up the
right year, when applied..
What I meant by LAST 4 COLUMNS, each sub-report produces a column of data
next to the previous sub-report, and the data source for each sub-report is a
query identicle to the others EXCEPT for the criteria, which selects 2 years
of data, one year later then the sub-report in front of it. So what happens
here is the first dataset in the first sub-report has 2008 and 2007 data,
when there is no 2008 data available for the current record it display 2007.
The dataset for the next sub-report will contain 2007 and 2006 data, but if
the current record contains not 2008 data this sub-report should display 2006
data, and so on for each sub-report inline.
Thank you for any help you can provide.

 

[BruceM]

I have learned that it is difficult to set a subreport's filter at runtime,
and in general to alter a subreport's recordset after the subreport has been
loaded. If the years could be one above the other rather than next to each
other all you would need to do is group the report, I think.
One thing to try are named queries for the five sub-reports. The criteria
for the date field would be two years ago, three years ago, and so forth.
You could also set the report's record source at run time. However, I can't
think offhand of a way to skip years that contain no data. It sournds as if
you are using separate queries for the subreports, so I'm not sure exactly
what you are trying to accomplish by resetting the filter. Some simplified
sample data may help in understanding the situation.
I looked at the syntax, but did not examine the situation as carefully as I
should have. The report's Print event runs for each record, I believe, so
you are trying to reset the filter for each record. The recordset will need
to be established before then.

Thanks I tried the error catch and it shows me what I already knew, but
hadn't psoted, the error is>>
Run-time error '2101':
The setting you entered isn't valid for this property.
[Continue] [End] [Debug] [Help]
and it happens on the first line that attempts to turn the Filter on, So
it
is not an issue with the actual filter, that seems to work and pull up the
right year, when applied..
What I meant by LAST 4 COLUMNS, each sub-report produces a column of data
next to the previous sub-report, and the data source for each sub-report
is a
query identicle to the others EXCEPT for the criteria, which selects 2
years
of data, one year later then the sub-report in front of it. So what
happens
here is the first dataset in the first sub-report has 2008 and 2007 data,
when there is no 2008 data available for the current record it display
2007.
The dataset for the next sub-report will contain 2007 and 2006 data, but
if
the current record contains not 2008 data this sub-report should display
2006
data, and so on for each sub-report inline.
Thank you for any help you can provide.

Try using this syntax for the filter string:
"Year(FHDate) = " & Year(Now)-2

I don't know if this will solve the problem, but it is something I
noticed.

You could try error handling that identifies the error:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

On Error GoTo ProcErr

{Your code here}

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in DetailPrint"
Resume ProcExit

End Sub

I'm not sure what you meant by "the last four columns".
in
message news:[email protected]...

 

[Programmer - wannaB]

I'm not sure what you are saying. when you talk about the record set needing
to be established, and set before runtime, they are. Each sub report is run
from it's own seperate query that each contain 2 differnt years of data. I
am trying to use the filter to specify which years data to display for each
individual record based on the data available in the first (query or
sub-report). This become very frustrating because it sort-of works when I
use "on error resume next", but not completely... it's like the code runs
through too fast to keep up with each record... And I dont think this can be
done with a crosstab query, bu tit is something like that, each the source of
each column changes dependant upon the data available ing the first column...
Any other suggestions or options you might have would be appreciated.
=================================

I have learned that it is difficult to set a subreport's filter at runtime,
and in general to alter a subreport's recordset after the subreport has been
loaded. If the years could be one above the other rather than next to each
other all you would need to do is group the report, I think.
One thing to try are named queries for the five sub-reports. The criteria
for the date field would be two years ago, three years ago, and so forth.
You could also set the report's record source at run time. However, I can't
think offhand of a way to skip years that contain no data. It sournds as if
you are using separate queries for the subreports, so I'm not sure exactly
what you are trying to accomplish by resetting the filter. Some simplified
sample data may help in understanding the situation.
I looked at the syntax, but did not examine the situation as carefully as I
should have. The report's Print event runs for each record, I believe, so
you are trying to reset the filter for each record. The recordset will need
to be established before then.

Thanks I tried the error catch and it shows me what I already knew, but
hadn't psoted, the error is>>
Run-time error '2101':
The setting you entered isn't valid for this property.
[Continue] [End] [Debug] [Help]
and it happens on the first line that attempts to turn the Filter on, So
it
is not an issue with the actual filter, that seems to work and pull up the
right year, when applied..
What I meant by LAST 4 COLUMNS, each sub-report produces a column of data
next to the previous sub-report, and the data source for each sub-report
is a
query identicle to the others EXCEPT for the criteria, which selects 2
years
of data, one year later then the sub-report in front of it. So what
happens
here is the first dataset in the first sub-report has 2008 and 2007 data,
when there is no 2008 data available for the current record it display
2007.
The dataset for the next sub-report will contain 2007 and 2006 data, but
if
the current record contains not 2008 data this sub-report should display
2006
data, and so on for each sub-report inline.
Thank you for any help you can provide.

Try using this syntax for the filter string:
"Year(FHDate) = " & Year(Now)-2

I don't know if this will solve the problem, but it is something I
noticed.

You could try error handling that identifies the error:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

On Error GoTo ProcErr

{Your code here}

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in DetailPrint"
Resume ProcExit

End Sub

I'm not sure what you meant by "the last four columns".
in
message So Sorry I forgot to post the Code...

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
On Error Resume Next
If Year(Me.rFP10!FHDate) < Year(Now()) Then
Me.rFP11.Report.FilterOn = True
Me.rFP11.Report.Filter = "year(FHDate)=(Year(Now()) -2)"
Me.rFP12.Report.FilterOn = True
Me.rFP12.Report.Filter = "year(FHDate)=(Year(Now()) -3)"
Me.rFP13.Report.FilterOn = True
Me.rFP13.Report.Filter = "year(FHDate)=(Year(Now()) -4)"
Me.rFP14.Report.FilterOn = True
Me.rFP14.Report.Filter = "year(FHDate)=(Year(Now()) -5)"
Else
Me.rFP11.Report.FilterOn = False
Me.rFP12.Report.FilterOn = False
Me.rFP13.Report.FilterOn = False
Me.rFP14.Report.FilterOn = False
End If
End Sub
======================
:

I have 5 sub-reports that I am trying to set filters, based on a date
field
in the first sub-report. This code ALMOST works, but only if I use
“On
Error
Resume Nextâ€, and then even thought I have stepped through it and
watched
it
go through the ELSE, and it works while stepping. When I run it with
DEBUG
OFF it doesn’t turn the filters off. The last 4 columns still show
data
for
2006 – 2003, where they should all five show data for 2008 – 2004.
Is there anyone out there who could please help me to write this code
correctly??? Thank you.

 

[BruceM]

Again, the print event runs for every page, or at least for every record.
By the time the Print event runs, the recordset has been loaded.

From what I can tell, you are looking at the year for Me.rFP10.FHDate for
the current record. Based on a comparison to Now() you want to set the
filter for all five subreports. After that you want to move to the next
record, run the comparison again, and set the filter for all five
subreports. Even if this could somehow work, your filter would be based on
the last record only, since all earlier filters would be overridden by the
final filter.

As I understand, if the first recordset contains records that include 2007
and 2008, you want to display 2008 only, and have the next subreport display
2007 data. However, if the first subreport contains no 2008 data you want
to display 2007 data, and have the next subreport display 2006 data. In
other words, if a year contains no data you want to skip the year, and have
the subreport show data from the next lower year that contains data.

One thing I can suggest that may be heading in the right direction is to
construct a totals query that groups by the year. If you could have the
years one above the other your problem would be solved: you would just need
to group by the year. However, in your case it sounds as if you will need
five separate queries. The first one could be a Top 1 query, or a query
that returns the Max value for the year expression. The second query would
need to include a subquery, and the third one would need a subquery within a
subquery, etc. You could try a Google groups search for:
Access query "second highest value"
or something like that. I have done some experimenting, but perhaps because
of the calculated field for finding the year such an exercise is beyond my
abilities. It may bge best to re-post in the Query group, or maybe in the
Report group if you intend to press ahead with applying a filter.

I'm not sure what you are saying. when you talk about the record set
needing
to be established, and set before runtime, they are. Each sub report is
run
from it's own seperate query that each contain 2 differnt years of data.
I
am trying to use the filter to specify which years data to display for
each
individual record based on the data available in the first (query or
sub-report). This become very frustrating because it sort-of works when
I
use "on error resume next", but not completely... it's like the code runs
through too fast to keep up with each record... And I dont think this can
be
done with a crosstab query, bu tit is something like that, each the source
of
each column changes dependant upon the data available ing the first
column...
Any other suggestions or options you might have would be appreciated.
=================================

I have learned that it is difficult to set a subreport's filter at
runtime,
and in general to alter a subreport's recordset after the subreport has
been
loaded. If the years could be one above the other rather than next to
each
other all you would need to do is group the report, I think.
One thing to try are named queries for the five sub-reports. The
criteria
for the date field would be two years ago, three years ago, and so forth.
You could also set the report's record source at run time. However, I
can't
think offhand of a way to skip years that contain no data. It sournds as
if
you are using separate queries for the subreports, so I'm not sure
exactly
what you are trying to accomplish by resetting the filter. Some
simplified
sample data may help in understanding the situation.
I looked at the syntax, but did not examine the situation as carefully as
I
should have. The report's Print event runs for each record, I believe,
so
you are trying to reset the filter for each record. The recordset will
need
to be established before then.

in
message news:[email protected]...

Thanks I tried the error catch and it shows me what I already knew, but
hadn't psoted, the error is>>
Run-time error '2101':
The setting you entered isn't valid for this property.
[Continue] [End] [Debug] [Help]
and it happens on the first line that attempts to turn the Filter on,
So
it
is not an issue with the actual filter, that seems to work and pull up
the
right year, when applied..
What I meant by LAST 4 COLUMNS, each sub-report produces a column of
data
next to the previous sub-report, and the data source for each
sub-report
is a
query identicle to the others EXCEPT for the criteria, which selects 2
years
of data, one year later then the sub-report in front of it. So what
happens
here is the first dataset in the first sub-report has 2008 and 2007
data,
when there is no 2008 data available for the current record it display
2007.
The dataset for the next sub-report will contain 2007 and 2006 data,
but
if
the current record contains not 2008 data this sub-report should
display
2006
data, and so on for each sub-report inline.
Thank you for any help you can provide.

:

Try using this syntax for the filter string:
"Year(FHDate) = " & Year(Now)-2

I don't know if this will solve the problem, but it is something I
noticed.

You could try error handling that identifies the error:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

On Error GoTo ProcErr

{Your code here}

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in DetailPrint"
Resume ProcExit

End Sub

I'm not sure what you meant by "the last four columns".
"Programmer - wannaB" <[email protected]>
wrote
in
message So Sorry I forgot to post the Code...

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
On Error Resume Next
If Year(Me.rFP10!FHDate) < Year(Now()) Then
Me.rFP11.Report.FilterOn = True
Me.rFP11.Report.Filter = "year(FHDate)=(Year(Now()) -2)"
Me.rFP12.Report.FilterOn = True
Me.rFP12.Report.Filter = "year(FHDate)=(Year(Now()) -3)"
Me.rFP13.Report.FilterOn = True
Me.rFP13.Report.Filter = "year(FHDate)=(Year(Now()) -4)"
Me.rFP14.Report.FilterOn = True
Me.rFP14.Report.Filter = "year(FHDate)=(Year(Now()) -5)"
Else
Me.rFP11.Report.FilterOn = False
Me.rFP12.Report.FilterOn = False
Me.rFP13.Report.FilterOn = False
Me.rFP14.Report.FilterOn = False
End If
End Sub
======================
:

I have 5 sub-reports that I am trying to set filters, based on a
date
field
in the first sub-report. This code ALMOST works, but only if I use
“On
Error
Resume Nextâ€, and then even thought I have stepped through it and
watched
it
go through the ELSE, and it works while stepping. When I run it
with
DEBUG
OFF it doesn’t turn the filters off. The last 4 columns still
show
data
for
2006 – 2003, where they should all five show data for 2008 – 2004.
Is there anyone out there who could please help me to write this
code
correctly??? Thank you.

 

[Programmer - wannaB]

Thank you, Bruce. You are exactly correct in your analysis of how you
understand what I am trying to accomplish. As I read your final paragraph, I
have an idea that might work. If modifiy the current queries to group on
[MAX(YEAR(FHDate)) and Indexnumber] the each "Indexnumber" (or Business)
would have only one record in each query, and that record would represent the
newest record of the 2 years that were included in each query. Each query
would then need only one sub-query to provide the grouping. What am I
missing here that will blow this out of the water??? Can you assist me in
the sub-query creation ??
I am thinking along these lines right now the first query would be only on
indexnumber and year, and I think this would be used in the WHERE. the main
query would be basically what I already have, adding a filed that combines
the indexnumber and the year to be matched against the grouping query... I
always forget which is called the sub-query, is that the first select or the
select from the select ???
Thanks So much Bruce, I would appreciate any additional comments,
confirmation or contradiction of my thoughts....
==========================================

Again, the print event runs for every page, or at least for every record.
By the time the Print event runs, the recordset has been loaded.

From what I can tell, you are looking at the year for Me.rFP10.FHDate for
the current record. Based on a comparison to Now() you want to set the
filter for all five subreports. After that you want to move to the next
record, run the comparison again, and set the filter for all five
subreports. Even if this could somehow work, your filter would be based on
the last record only, since all earlier filters would be overridden by the
final filter.

As I understand, if the first recordset contains records that include 2007
and 2008, you want to display 2008 only, and have the next subreport display
2007 data. However, if the first subreport contains no 2008 data you want
to display 2007 data, and have the next subreport display 2006 data. In
other words, if a year contains no data you want to skip the year, and have
the subreport show data from the next lower year that contains data.

One thing I can suggest that may be heading in the right direction is to
construct a totals query that groups by the year. If you could have the
years one above the other your problem would be solved: you would just need
to group by the year. However, in your case it sounds as if you will need
five separate queries. The first one could be a Top 1 query, or a query
that returns the Max value for the year expression. The second query would
need to include a subquery, and the third one would need a subquery within a
subquery, etc. You could try a Google groups search for:
Access query "second highest value"
or something like that. I have done some experimenting, but perhaps because
of the calculated field for finding the year such an exercise is beyond my
abilities. It may bge best to re-post in the Query group, or maybe in the
Report group if you intend to press ahead with applying a filter.

I'm not sure what you are saying. when you talk about the record set
needing
to be established, and set before runtime, they are. Each sub report is
run
from it's own seperate query that each contain 2 differnt years of data.
I
am trying to use the filter to specify which years data to display for
each
individual record based on the data available in the first (query or
sub-report). This become very frustrating because it sort-of works when
I
use "on error resume next", but not completely... it's like the code runs
through too fast to keep up with each record... And I dont think this can
be
done with a crosstab query, bu tit is something like that, each the source
of
each column changes dependant upon the data available ing the first
column...
Any other suggestions or options you might have would be appreciated.
=================================

I have learned that it is difficult to set a subreport's filter at
runtime,
and in general to alter a subreport's recordset after the subreport has
been
loaded. If the years could be one above the other rather than next to
each
other all you would need to do is group the report, I think.
One thing to try are named queries for the five sub-reports. The
criteria
for the date field would be two years ago, three years ago, and so forth.
You could also set the report's record source at run time. However, I
can't
think offhand of a way to skip years that contain no data. It sournds as
if
you are using separate queries for the subreports, so I'm not sure
exactly
what you are trying to accomplish by resetting the filter. Some
simplified
sample data may help in understanding the situation.
I looked at the syntax, but did not examine the situation as carefully as
I
should have. The report's Print event runs for each record, I believe,
so
you are trying to reset the filter for each record. The recordset will
need
to be established before then.

in
message Thanks I tried the error catch and it shows me what I already knew, but
hadn't psoted, the error is>>
Run-time error '2101':
The setting you entered isn't valid for this property.
[Continue] [End] [Debug] [Help]
and it happens on the first line that attempts to turn the Filter on,
So
it
is not an issue with the actual filter, that seems to work and pull up
the
right year, when applied..
What I meant by LAST 4 COLUMNS, each sub-report produces a column of
data
next to the previous sub-report, and the data source for each
sub-report
is a
query identicle to the others EXCEPT for the criteria, which selects 2
years
of data, one year later then the sub-report in front of it. So what
happens
here is the first dataset in the first sub-report has 2008 and 2007
data,
when there is no 2008 data available for the current record it display
2007.
The dataset for the next sub-report will contain 2007 and 2006 data,
but
if
the current record contains not 2008 data this sub-report should
display
2006
data, and so on for each sub-report inline.
Thank you for any help you can provide.

:

Try using this syntax for the filter string:
"Year(FHDate) = " & Year(Now)-2

I don't know if this will solve the problem, but it is something I
noticed.

You could try error handling that identifies the error:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

On Error GoTo ProcErr

{Your code here}

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in DetailPrint"
Resume ProcExit

End Sub

I'm not sure what you meant by "the last four columns".
"Programmer - wannaB" <[email protected]>
wrote
in
message So Sorry I forgot to post the Code...

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
On Error Resume Next
If Year(Me.rFP10!FHDate) < Year(Now()) Then
Me.rFP11.Report.FilterOn = True
Me.rFP11.Report.Filter = "year(FHDate)=(Year(Now()) -2)"
Me.rFP12.Report.FilterOn = True
Me.rFP12.Report.Filter = "year(FHDate)=(Year(Now()) -3)"
Me.rFP13.Report.FilterOn = True
Me.rFP13.Report.Filter = "year(FHDate)=(Year(Now()) -4)"
Me.rFP14.Report.FilterOn = True
Me.rFP14.Report.Filter = "year(FHDate)=(Year(Now()) -5)"
Else
Me.rFP11.Report.FilterOn = False
Me.rFP12.Report.FilterOn = False
Me.rFP13.Report.FilterOn = False
Me.rFP14.Report.FilterOn = False
End If
End Sub
======================
:

I have 5 sub-reports that I am trying to set filters, based on a
date
field
in the first sub-report. This code ALMOST works, but only if I use
“On
Error
Resume Nextâ€, and then even thought I have stepped through it and
watched
it
go through the ELSE, and it works while stepping. When I run it
with
DEBUG
OFF it doesn’t turn the filters off. The last 4 columns still
show
data
for
2006 – 2003, where they should all five show data for 2008 – 2004.
Is there anyone out there who could please help me to write this
code
correctly??? Thank you.

 

[BruceM]

I can't say for sure that I follow your question, and in any case this is
starting to stretch my knowledge about SQL. However, I can say from my
experience with subqueries such as this that there is an inner join between
a table and an alias of that table. That is, there are two instances of the
same table, so they need different names.

The subquery is the second SELECT. It could be a named query instead.

Again, this is getting out of my area of knowledge. I would like to help,
but I may not be able to do so. The queries group may a better place to
post the question. In any case, I won't be near my computer very much
today.

Thank you, Bruce. You are exactly correct in your analysis of how you
understand what I am trying to accomplish. As I read your final
paragraph, I
have an idea that might work. If modifiy the current queries to group on
[MAX(YEAR(FHDate)) and Indexnumber] the each "Indexnumber" (or Business)
would have only one record in each query, and that record would represent
the
newest record of the 2 years that were included in each query. Each query
would then need only one sub-query to provide the grouping. What am I
missing here that will blow this out of the water??? Can you assist me in
the sub-query creation ??
I am thinking along these lines right now the first query would be only on
indexnumber and year, and I think this would be used in the WHERE. the
main
query would be basically what I already have, adding a filed that combines
the indexnumber and the year to be matched against the grouping query...
I
always forget which is called the sub-query, is that the first select or
the
select from the select ???
Thanks So much Bruce, I would appreciate any additional comments,
confirmation or contradiction of my thoughts....
==========================================

Again, the print event runs for every page, or at least for every record.
By the time the Print event runs, the recordset has been loaded.

From what I can tell, you are looking at the year for Me.rFP10.FHDate for
the current record. Based on a comparison to Now() you want to set the
filter for all five subreports. After that you want to move to the next
record, run the comparison again, and set the filter for all five
subreports. Even if this could somehow work, your filter would be based
on
the last record only, since all earlier filters would be overridden by
the
final filter.

As I understand, if the first recordset contains records that include
2007
and 2008, you want to display 2008 only, and have the next subreport
display
2007 data. However, if the first subreport contains no 2008 data you
want
to display 2007 data, and have the next subreport display 2006 data. In
other words, if a year contains no data you want to skip the year, and
have
the subreport show data from the next lower year that contains data.

One thing I can suggest that may be heading in the right direction is to
construct a totals query that groups by the year. If you could have the
years one above the other your problem would be solved: you would just
need
to group by the year. However, in your case it sounds as if you will
need
five separate queries. The first one could be a Top 1 query, or a query
that returns the Max value for the year expression. The second query
would
need to include a subquery, and the third one would need a subquery
within a
subquery, etc. You could try a Google groups search for:
Access query "second highest value"
or something like that. I have done some experimenting, but perhaps
because
of the calculated field for finding the year such an exercise is beyond
my
abilities. It may bge best to re-post in the Query group, or maybe in
the
Report group if you intend to press ahead with applying a filter.

in
message news:[email protected]...

I'm not sure what you are saying. when you talk about the record set
needing
to be established, and set before runtime, they are. Each sub report
is
run
from it's own seperate query that each contain 2 differnt years of
data.
I
am trying to use the filter to specify which years data to display for
each
individual record based on the data available in the first (query or
sub-report). This become very frustrating because it sort-of works
when
I
use "on error resume next", but not completely... it's like the code
runs
through too fast to keep up with each record... And I dont think this
can
be
done with a crosstab query, bu tit is something like that, each the
source
of
each column changes dependant upon the data available ing the first
column...
Any other suggestions or options you might have would be appreciated.
=================================
:
I have learned that it is difficult to set a subreport's filter at
runtime,
and in general to alter a subreport's recordset after the subreport
has
been
loaded. If the years could be one above the other rather than next to
each
other all you would need to do is group the report, I think.
One thing to try are named queries for the five sub-reports. The
criteria
for the date field would be two years ago, three years ago, and so
forth.
You could also set the report's record source at run time. However, I
can't
think offhand of a way to skip years that contain no data. It sournds
as
if
you are using separate queries for the subreports, so I'm not sure
exactly
what you are trying to accomplish by resetting the filter. Some
simplified
sample data may help in understanding the situation.
I looked at the syntax, but did not examine the situation as carefully
as
I
should have. The report's Print event runs for each record, I
believe,
so
you are trying to reset the filter for each record. The recordset
will
need
to be established before then.

"Programmer - wannaB" <[email protected]>
wrote
in
message Thanks I tried the error catch and it shows me what I already knew,
but
hadn't psoted, the error is>>
Run-time error '2101':
The setting you entered isn't valid for this property.
[Continue] [End] [Debug] [Help]
and it happens on the first line that attempts to turn the Filter
on,
So
it
is not an issue with the actual filter, that seems to work and pull
up
the
right year, when applied..
What I meant by LAST 4 COLUMNS, each sub-report produces a column of
data
next to the previous sub-report, and the data source for each
sub-report
is a
query identicle to the others EXCEPT for the criteria, which selects
2
years
of data, one year later then the sub-report in front of it. So what
happens
here is the first dataset in the first sub-report has 2008 and 2007
data,
when there is no 2008 data available for the current record it
display
2007.
The dataset for the next sub-report will contain 2007 and 2006 data,
but
if
the current record contains not 2008 data this sub-report should
display
2006
data, and so on for each sub-report inline.
Thank you for any help you can provide.

:

Try using this syntax for the filter string:
"Year(FHDate) = " & Year(Now)-2

I don't know if this will solve the problem, but it is something I
noticed.

You could try error handling that identifies the error:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

On Error GoTo ProcErr

{Your code here}

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in DetailPrint"
Resume ProcExit

End Sub

I'm not sure what you meant by "the last four columns".
"Programmer - wannaB" <[email protected]>
wrote
in
message So Sorry I forgot to post the Code...

Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)
On Error Resume Next
If Year(Me.rFP10!FHDate) < Year(Now()) Then
Me.rFP11.Report.FilterOn = True
Me.rFP11.Report.Filter = "year(FHDate)=(Year(Now()) -2)"
Me.rFP12.Report.FilterOn = True
Me.rFP12.Report.Filter = "year(FHDate)=(Year(Now()) -3)"
Me.rFP13.Report.FilterOn = True
Me.rFP13.Report.Filter = "year(FHDate)=(Year(Now()) -4)"
Me.rFP14.Report.FilterOn = True
Me.rFP14.Report.Filter = "year(FHDate)=(Year(Now()) -5)"
Else
Me.rFP11.Report.FilterOn = False
Me.rFP12.Report.FilterOn = False
Me.rFP13.Report.FilterOn = False
Me.rFP14.Report.FilterOn = False
End If
End Sub
======================
:

I have 5 sub-reports that I am trying to set filters, based on a
date
field
in the first sub-report. This code ALMOST works, but only if I
use
“On
Error
Resume Nextâ€, and then even thought I have stepped through it
and
watched
it
go through the ELSE, and it works while stepping. When I run it
with
DEBUG
OFF it doesn’t turn the filters off. The last 4 columns still
show
data
for
2006 – 2003, where they should all five show data for 2008 –
2004.
Is there anyone out there who could please help me to write this
code
correctly??? Thank you.