Report from table or from query?

[Jim Orson]

Hello,

I have a small, simple database (<50 records and only one table) that works
just fine. There is a report that uses a query as the record source. The
query is built on the one table and contains criteria (Is Not Null) for one
date field to limit the number of records. The query also contains sorting
instructions.

I was experimenting with a copy of the database file and found that I could
change the report record source to the actual table rather than the query
and use the 'filter' and 'filter on' properties to limit the date field to
Is Not Null. I can also sort at the report level. The report seems to work
properly using either of these two methods. My question is, which method is
better, if either? This second method eliminates the query entirely which
seems simpler. Any thoughts please?

Thanks!

Jim Orson...

 

[fredg]

Hello,

I have a small, simple database (<50 records and only one table)
that works just fine. There is a report that uses a query as the
record source. The query is built on the one table and contains
criteria (Is Not Null) for one date field to limit the number of
records. The query also contains sorting instructions.

I was experimenting with a copy of the database file and found that
I could change the report record source to the actual table rather
than the query and use the 'filter' and 'filter on' properties to
limit the date field to Is Not Null. I can also sort at the report
level. The report seems to work properly using either of these two
methods. My question is, which method is better, if either? This
second method eliminates the query entirely which seems simpler.
Any thoughts please?

Thanks!

Jim Orson...

Jim,
Any sort done in the query is irrelevant to the sort order in a
report.

The place to sort a report is in the report's Sorting and Grouping
dialog:
View + Sorting and Grouping

Also, you do not even need to filter in the report, or even use a
query, if you use a command button on a form to open the report.
Set it's Click event to:

DoCmd.OpenReport acPreview, , "[YourField] Is Null"

Only those records in which that field is null will appear.

This will allow you to use the same report with various criteria,
depending upon how you code the event.

 

[Jim Orson]

Fred,

Great information, very useful. Thank you very much!

Jim Orson...

Hello,

I have a small, simple database (<50 records and only one table)
that works just fine. There is a report that uses a query as the
record source. The query is built on the one table and contains
criteria (Is Not Null) for one date field to limit the number of
records. The query also contains sorting instructions.

I was experimenting with a copy of the database file and found that
I could change the report record source to the actual table rather
than the query and use the 'filter' and 'filter on' properties to
limit the date field to Is Not Null. I can also sort at the report
level. The report seems to work properly using either of these two
methods. My question is, which method is better, if either? This
second method eliminates the query entirely which seems simpler.
Any thoughts please?

Thanks!

Jim Orson...

Jim,
Any sort done in the query is irrelevant to the sort order in a
report.

The place to sort a report is in the report's Sorting and Grouping
dialog:
View + Sorting and Grouping

Also, you do not even need to filter in the report, or even use a
query, if you use a command button on a form to open the report.
Set it's Click event to:

DoCmd.OpenReport acPreview, , "[YourField] Is Null"

Only those records in which that field is null will appear.

This will allow you to use the same report with various criteria,
depending upon how you code the event.