Report not pulling up all records as sorted by query

[Guest]

I have a report linked to a query. When I run the query by itself it pulls up
all records based on two criteria. When I run the report, it only pulls up
one record according to the criteria in the query, and this record is the
last record within the table that satisfies the criteria. Does anyone know
why my report won't show all records that satisfy the criteria?

 

[John Vinson]

I have a report linked to a query. When I run the query by itself it pulls up
all records based on two criteria. When I run the report, it only pulls up
one record according to the criteria in the query, and this record is the
last record within the table that satisfies the criteria. Does anyone know
why my report won't show all records that satisfy the criteria?

Care to give us a little help here? We cannot see your report or the
query.

Please post the SQL view of the Query, the Recordsource property of
the Report, and describe what's in the Sorting and Grouping property
of the Report.


John W. Vinson[MVP]

 

[Guest]

Hi,

SQL view of query:

SELECT [Daily Plant Input].Date, [Daily Plant Input].Shift, [Daily Plant
Input].Seam, [Daily Plant Input].[Feeder Speed %], [Daily Plant Input].[Start
(time)], [Daily Plant Input].[Finish (time)], [Daily Plant Input].[Operating
Hrs], [Daily Plant Input].[Plant Cutpoint SG], [Daily Plant Input].[ROM
Screen Feed Tonnes], [Daily Plant Input].[Plant Feed Tonnes], [Daily Plant
Input].[Plant Feed Ash%], [Daily Plant Input].[Plant Feed Moisture %], [Daily
Plant Input].[Plant Feed FSI], [Daily Plant Input].[Fines SP Tonnes], [Daily
Plant Input].[Fines SP Ash%], [Daily Plant Input].[Fines SP Moisture %],
[Daily Plant Input].[Fines SP FSI], [Daily Plant Input].[Est Drum
Proportion], [Daily Plant Input].[Drum Product Ash %], [Daily Plant
Input].[Drum Product Moisture %], [Daily Plant Input].[Drum Product FSI],
[Daily Plant Input].[Fine Sieve Ash %], [Daily Plant Input].[Filter Cake Ash
%], [Daily Plant Input].[Lab Reject Ash %], [Daily Plant Input].[Reject
Moisture %], [Daily Plant Input].[Combined Product Tonnes], [Daily Plant
Input].[Lab Product Ash %], [Daily Plant Input].[Lab Product Moisture %],
[Daily Plant Input].[Lab Product FSI]
FROM [Daily Plant Input]
WHERE ((([Daily Plant Input].Date)=[Date DD/MM/YEAR]) AND (([Daily Plant
Input].Shift)=[D or N]));

Record Source Property of Report says: Daily Plant Input Query 1

Sorting and Grouping Property of the report:
Field/Expression
Date
Group Header: Yes
Group Footer: No
Group on: Month
Group Interval: 1
Keep together: No

Shift
Group Header: Yes
Group Footer: No
Group on: Each value
Group Interval: 1
Keep together: No

Date
Group Header: No
Group Footer: No
Group on: Each value
Group Interval: 1
Keep together: No

Each of these have sort order of ascending.

I hope this helps you help me!
Thanks,
Melanie

 

[Guest]

Melanie:

Whether this has a bearing on your problem I'm not sure but there are a
couple of things about your query worth mentioning:

1. Parameters which take a date/time value, especially when the parameter
value is to be entered in short date format are best declared. Otherwise its
possible that the value could be interpreted as an arithmetical expression,
so start the query with:

PARAMETERS [Date DD/MM/YEAR] DateTime, [D or N] Text ( 1 );
SELECT etc.

2. I note that you've used Date as a column name. This is best avoided as
it could be confused with the built in Date function. Its better to use more
explicit column names like SampleDate, TransactionDate or whatever's
appropriate, bit if you do use Date then enclose it in brackets [Date] to
tell Access its an object.

I don't really see how either of these could be causing the behaviour you
are experiencing, however, but one never knows!

Ken Sheridan
Stafford, England

Hi,

SQL view of query:

SELECT [Daily Plant Input].Date, [Daily Plant Input].Shift, [Daily Plant
Input].Seam, [Daily Plant Input].[Feeder Speed %], [Daily Plant Input].[Start
(time)], [Daily Plant Input].[Finish (time)], [Daily Plant Input].[Operating
Hrs], [Daily Plant Input].[Plant Cutpoint SG], [Daily Plant Input].[ROM
Screen Feed Tonnes], [Daily Plant Input].[Plant Feed Tonnes], [Daily Plant
Input].[Plant Feed Ash%], [Daily Plant Input].[Plant Feed Moisture %], [Daily
Plant Input].[Plant Feed FSI], [Daily Plant Input].[Fines SP Tonnes], [Daily
Plant Input].[Fines SP Ash%], [Daily Plant Input].[Fines SP Moisture %],
[Daily Plant Input].[Fines SP FSI], [Daily Plant Input].[Est Drum
Proportion], [Daily Plant Input].[Drum Product Ash %], [Daily Plant
Input].[Drum Product Moisture %], [Daily Plant Input].[Drum Product FSI],
[Daily Plant Input].[Fine Sieve Ash %], [Daily Plant Input].[Filter Cake Ash
%], [Daily Plant Input].[Lab Reject Ash %], [Daily Plant Input].[Reject
Moisture %], [Daily Plant Input].[Combined Product Tonnes], [Daily Plant
Input].[Lab Product Ash %], [Daily Plant Input].[Lab Product Moisture %],
[Daily Plant Input].[Lab Product FSI]
FROM [Daily Plant Input]
WHERE ((([Daily Plant Input].Date)=[Date DD/MM/YEAR]) AND (([Daily Plant
Input].Shift)=[D or N]));

Record Source Property of Report says: Daily Plant Input Query 1

Sorting and Grouping Property of the report:
Field/Expression
Date
Group Header: Yes
Group Footer: No
Group on: Month
Group Interval: 1
Keep together: No

Shift
Group Header: Yes
Group Footer: No
Group on: Each value
Group Interval: 1
Keep together: No

Date
Group Header: No
Group Footer: No
Group on: Each value
Group Interval: 1
Keep together: No

Each of these have sort order of ascending.

I hope this helps you help me!
Thanks,
Melanie

Care to give us a little help here? We cannot see your report or the
query.

Please post the SQL view of the Query, the Recordsource property of
the Report, and describe what's in the Sorting and Grouping property
of the Report.


John W. Vinson[MVP]

 

[Guest]

The solution was simple. I had all the details that I wanted in my report in
the header section instead of in the details section. I moved them and it
works perfectly!

I need to get a better 'how to' book I guess.