How to skip records on a report

[GL]

Hell

I have a table of Items with the fields ItemCode, Kind, Type, UsedFro
and UsedTo
My goal is to take a report that shows for every item, if the tabl
includes similar items (i.e. of same Kind and Type) that they wil
not be in use for the same period time
The solution I’ve found to do this is to use a report, say i
myReport1, with a subreport, mySubReport1
The Record Source for myReport is the Items table and the Recor
Source for mySubReport1 is the following quey
SELECT Items.ItemCode, Items.Kind, Items.Type, Items.UsedFrom
Items.UsedT
FROM Item
WHERE (((Items.Kind) Like [Reports]![MyReport1]![Kind]) AN
((Items.Type) Like [Reports]![MyReport1]![Type]) AN
((Items.UsedFrom) Not Between [Reports]![MyReport1]![UsedFrom] An
[Reports]![MyReport1]![UsedTo]) AND ((Items.UsedTo) Not Betwee
[Reports]![MyReport1]![UsedFrom] An
[Reports]![MyReport1]![UsedTo])

This works fine, I get some records on the report (the interestin
ones) and their similar used for other periods, according to th
Query criteria, on the subreport.
The problem is that I also get hundreds of records on the reports tha
provide no records on the subreport (the query gives emt
recordsets)
Do you know any way to force the report to show only the record whic
provide data on the subreport and skip the rest ones

Thank
G

 

[Marshall Barton]

I have a table of Items with the fields ItemCode, Kind, Type, UsedFrom
and UsedTo.
My goal is to take a report that shows for every item, if the table
includes similar items (i.e. of same Kind and Type) that they will
not be in use for the same period time.
The solution I’ve found to do this is to use a report, say it
myReport1, with a subreport, mySubReport1.
The Record Source for myReport is the Items table and the Record
Source for mySubReport1 is the following quey
SELECT Items.ItemCode, Items.Kind, Items.Type, Items.UsedFrom,
Items.UsedTo
FROM Items
WHERE (((Items.Kind) Like [Reports]![MyReport1]![Kind]) AND
((Items.Type) Like [Reports]![MyReport1]![Type]) AND
((Items.UsedFrom) Not Between [Reports]![MyReport1]![UsedFrom] And
[Reports]![MyReport1]![UsedTo]) AND ((Items.UsedTo) Not Between
[Reports]![MyReport1]![UsedFrom] And
[Reports]![MyReport1]![UsedTo]))

This works fine, I get some records on the report (the interesting
ones) and their similar used for other periods, according to the
Query criteria, on the subreport.
The problem is that I also get hundreds of records on the reports that
provide no records on the subreport (the query gives emty
recordsets).
Do you know any way to force the report to show only the record which
provide data on the subreport and skip the rest ones.

If the record's data controls are in the same section that
contains the subreport, you can use a line of code in the
section's Format event procedure:

Cancel = Not Me.subreportcontrol.HasData

I'd like to think there's a way to do this in the main
report's record source query, but I'm not sure it would be
worth it.

 

[Guest]

Dear Marsh

Thanks for replying.
Since the record's data controls and the subreport are in the detail section
of the report I add the code you sugest in the detail’s section on format
event.
Unfortunately it doesn’t work.
When I run it I take the message
“Run-time error ‘438’
Object doesn’t support this property or methodâ€

GL

Ο χÏήστης "Marshall Barton" έγγÏαψε:

I have a table of Items with the fields ItemCode, Kind, Type, UsedFrom
and UsedTo.
My goal is to take a report that shows for every item, if the table
includes similar items (i.e. of same Kind and Type) that they will
not be in use for the same period time.
The solution I’ve found to do this is to use a report, say it
myReport1, with a subreport, mySubReport1.
The Record Source for myReport is the Items table and the Record
Source for mySubReport1 is the following quey
SELECT Items.ItemCode, Items.Kind, Items.Type, Items.UsedFrom,
Items.UsedTo
FROM Items
WHERE (((Items.Kind) Like [Reports]![MyReport1]![Kind]) AND
((Items.Type) Like [Reports]![MyReport1]![Type]) AND
((Items.UsedFrom) Not Between [Reports]![MyReport1]![UsedFrom] And
[Reports]![MyReport1]![UsedTo]) AND ((Items.UsedTo) Not Between
[Reports]![MyReport1]![UsedFrom] And
[Reports]![MyReport1]![UsedTo]))

This works fine, I get some records on the report (the interesting
ones) and their similar used for other periods, according to the
Query criteria, on the subreport.
The problem is that I also get hundreds of records on the reports that
provide no records on the subreport (the query gives emty
recordsets).
Do you know any way to force the report to show only the record which
provide data on the subreport and skip the rest ones.

If the record's data controls are in the same section that
contains the subreport, you can use a line of code in the
section's Format event procedure:

Cancel = Not Me.subreportcontrol.HasData

I'd like to think there's a way to do this in the main
report's record source query, but I'm not sure it would be
worth it.

 

[Marshall Barton]

Aaarrrgggghhhh. Sorry about wasting your time, I meant to
say:

Cancel = Not Me.subreportcontrol.REPORT.HasData
--
Marsh
MVP [MS Access]

Since the record's data controls and the subreport are in the detail section
of the report I add the code you sugest in the detail’s section on format
event.
Unfortunately it doesn’t work.
When I run it I take the message
“Run-time error ‘438’
Object doesn’t support this property or method”

I have a table of Items with the fields ItemCode, Kind, Type, UsedFrom
and UsedTo.
My goal is to take a report that shows for every item, if the table
includes similar items (i.e. of same Kind and Type) that they will
not be in use for the same period time.
The solution I’ve found to do this is to use a report, say it
myReport1, with a subreport, mySubReport1.
The Record Source for myReport is the Items table and the Record
Source for mySubReport1 is the following quey
SELECT Items.ItemCode, Items.Kind, Items.Type, Items.UsedFrom,
Items.UsedTo
FROM Items
WHERE (((Items.Kind) Like [Reports]![MyReport1]![Kind]) AND
((Items.Type) Like [Reports]![MyReport1]![Type]) AND
((Items.UsedFrom) Not Between [Reports]![MyReport1]![UsedFrom] And
[Reports]![MyReport1]![UsedTo]) AND ((Items.UsedTo) Not Between
[Reports]![MyReport1]![UsedFrom] And
[Reports]![MyReport1]![UsedTo]))

This works fine, I get some records on the report (the interesting
ones) and their similar used for other periods, according to the
Query criteria, on the subreport.
The problem is that I also get hundreds of records on the reports that
provide no records on the subreport (the query gives emty
recordsets).
Do you know any way to force the report to show only the record which
provide data on the subreport and skip the rest ones.

If the record's data controls are in the same section that
contains the subreport, you can use a line of code in the
section's Format event procedure:

Cancel = Not Me.subreportcontrol.HasData

I'd like to think there's a way to do this in the main
report's record source query, but I'm not sure it would be
worth it.

 

[Guest]

Couldn't you use the query wizard to create a duplicates query to get records
that have a duplicate based on your criteria. Add the other fields (the
period of time used) to the query as well and you may not need a subreport.