Query for report won't sort

[Jan Il]

Hi all - Access 2002 - WinME

I have a report based on a query. I have a form from which the data is
sorted for another Form by Employee Name, Contractor Name, Job Location, and
Job Type, via command buttons for each category. There's also From and To
date period controls. Any combination of the categories and date periods
selected can be sued to sort the data in the second Form. There is a command
button on the Form that opens the report for preview, and one to print the
report. The filter form sorts the records perfectly, and they are displayed
as they should be according to the selections entered on the filter form.
However, the report does will not filter the records according the to Form.
It will display the correct date period on the report as it is on the form,
but, it will not limit the list of records to the correct sorting.

I have recreated the form, query and report form several times, and it still
will not sort properly. I have gone over everything I can think of, and I
can't find the problem. I am not sure if it is in the query since it works
properly with the Form, but, I can't find anything in the report form that
might be causing the problem. The SQL for the query the form and report are
based upon is as follows;

SELECT tblLaborCosts.EmpName, tblLaborCosts.EmpID, tblLaborCosts.JobDate,
tblLaborCosts.ContractorName, tblLaborCosts.JobType,
tblLaborCosts.JobLocation, tblLaborCosts.Manhours, tblLaborCosts.HourlyRate,
Sum([Manhours]*[HourlyRate]) AS TotalCost
FROM tblLaborCosts
GROUP BY tblLaborCosts.EmpName, tblLaborCosts.EmpID, tblLaborCosts.JobDate,
tblLaborCosts.ContractorName, tblLaborCosts.JobType,
tblLaborCosts.JobLocation, tblLaborCosts.Manhours, tblLaborCosts.HourlyRate
HAVING (((tblLaborCosts.JobDate) Between [Forms]![frmLaborFilter]![Text62]
And [Forms]![frmLaborFilter]![Text64])) OR
((([Forms]![frmLaborFilter]![Text62]) Is Null)) OR
((([Forms]![frmLaborFilter]![Text64]) Is Null));

Is three something I set up wrong that prevents the report from sorting in
accordance with the form data?

Cheers, and Happy Holidays!
Jan

 

[Joe Fallon]

The query used by the report should NOT be sorted. It is a waste of time.
(The report ignores it.)
The report has its own sorting and grouping feature that should be used.

 

[Jan Il]

Hi Joe!

The query used by the report should NOT be sorted. It is a waste of time.
(The report ignores it.)
The report has its own sorting and grouping feature that should be used.

I truly apologize if I'm not explaining too well.

I am not trying to sort the records on the report form, but, using a filter
form controls to call up a group of select records I want the report to
print, such as; all records for Mass Elec (selected from the ContractorName
combo box) between January 1, 2003 and December 31, 2003 (entered in the
From-To date controls). When I click the respective command button, all the
records for Mass Elec between 1/01/03 and 12/31/03 are called up and
displayed in the viewing form that is opened when the button is clicked. It
is the same group of records that I want to print in the report. I have a
button on the viewing form which will allow the report to be previewed, and
one that will print the report.

The correct records are being called up in the viewing form, but, the report
does not display or print only those records. It displays and prints all of
the records in the table. If I should not use the filter form controls to
select the group of records to be printed in the report as needed, and as
displayed in the related viewing form, what other method of selecting them
should I use to get the report to print out only that select group of
records for that specific date period?

Thank you very much for our time, I truly do appreciate your help.

Happy Holidays!
Jan

--
Joe Fallon
Access MVP

Hi all - Access 2002 - WinME

I have a report based on a query. I have a form from which the data is
sorted for another Form by Employee Name, Contractor Name, Job Location, and
Job Type, via command buttons for each category. There's also From and To
date period controls. Any combination of the categories and date periods
selected can be sued to sort the data in the second Form. There is a command
button on the Form that opens the report for preview, and one to print the
report. The filter form sorts the records perfectly, and they are displayed
as they should be according to the selections entered on the filter form.
However, the report does will not filter the records according the to Form.
It will display the correct date period on the report as it is on the form,
but, it will not limit the list of records to the correct sorting.

I have recreated the form, query and report form several times, and it still
will not sort properly. I have gone over everything I can think of, and I
can't find the problem. I am not sure if it is in the query since it works
properly with the Form, but, I can't find anything in the report form that
might be causing the problem. The SQL for the query the form and report are
based upon is as follows;

SELECT tblLaborCosts.EmpName, tblLaborCosts.EmpID, tblLaborCosts.JobDate,
tblLaborCosts.ContractorName, tblLaborCosts.JobType,
tblLaborCosts.JobLocation, tblLaborCosts.Manhours, tblLaborCosts.HourlyRate,
Sum([Manhours]*[HourlyRate]) AS TotalCost
FROM tblLaborCosts
GROUP BY tblLaborCosts.EmpName, tblLaborCosts.EmpID, tblLaborCosts.JobDate,
tblLaborCosts.ContractorName, tblLaborCosts.JobType,
tblLaborCosts.JobLocation, tblLaborCosts.Manhours, tblLaborCosts.HourlyRate
HAVING (((tblLaborCosts.JobDate) Between [Forms]![frmLaborFilter]![Text62]
And [Forms]![frmLaborFilter]![Text64])) OR
((([Forms]![frmLaborFilter]![Text62]) Is Null)) OR
((([Forms]![frmLaborFilter]![Text64]) Is Null));

Is three something I set up wrong that prevents the report from sorting in
accordance with the form data?

Cheers, and Happy Holidays!
Jan

 

[fredg]

Hi all - Access 2002 - WinME

I have a report based on a query. I have a form from which the data is
sorted for another Form by Employee Name, Contractor Name, Job Location, and
Job Type, via command buttons for each category. There's also From and To
date period controls. Any combination of the categories and date periods
selected can be sued to sort the data in the second Form. There is a command
button on the Form that opens the report for preview, and one to print the
report. The filter form sorts the records perfectly, and they are displayed
as they should be according to the selections entered on the filter form.
However, the report does will not filter the records according the to Form.
It will display the correct date period on the report as it is on the form,
but, it will not limit the list of records to the correct sorting.

I have recreated the form, query and report form several times, and it still
will not sort properly. I have gone over everything I can think of, and I
can't find the problem. I am not sure if it is in the query since it works
properly with the Form, but, I can't find anything in the report form that
might be causing the problem. The SQL for the query the form and report are
based upon is as follows;

SELECT tblLaborCosts.EmpName, tblLaborCosts.EmpID, tblLaborCosts.JobDate,
tblLaborCosts.ContractorName, tblLaborCosts.JobType,
tblLaborCosts.JobLocation, tblLaborCosts.Manhours, tblLaborCosts.HourlyRate,
Sum([Manhours]*[HourlyRate]) AS TotalCost
FROM tblLaborCosts
GROUP BY tblLaborCosts.EmpName, tblLaborCosts.EmpID, tblLaborCosts.JobDate,
tblLaborCosts.ContractorName, tblLaborCosts.JobType,
tblLaborCosts.JobLocation, tblLaborCosts.Manhours, tblLaborCosts.HourlyRate
HAVING (((tblLaborCosts.JobDate) Between [Forms]![frmLaborFilter]![Text62]
And [Forms]![frmLaborFilter]![Text64])) OR
((([Forms]![frmLaborFilter]![Text62]) Is Null)) OR
((([Forms]![frmLaborFilter]![Text64]) Is Null));

Is three something I set up wrong that prevents the report from sorting in
accordance with the form data?

Cheers, and Happy Holidays!
Jan

Jan,
The sort order of a form, table, or query is irrelevant to the sort
order of a report.
To sort a report, in Report Design View,
click View + Sorting and Grouping.
Set the sort order there.

 

[Jan Il]

Hi Fred!

Hi all - Access 2002 - WinME

I have a report based on a query. I have a form from which the data is
sorted for another Form by Employee Name, Contractor Name, Job Location, and
Job Type, via command buttons for each category. There's also From and To
date period controls. Any combination of the categories and date periods
selected can be sued to sort the data in the second Form. There is a command
button on the Form that opens the report for preview, and one to print the
report. The filter form sorts the records perfectly, and they are displayed
as they should be according to the selections entered on the filter form.
However, the report does will not filter the records according the to Form.
It will display the correct date period on the report as it is on the form,
but, it will not limit the list of records to the correct sorting.

I have recreated the form, query and report form several times, and it still
will not sort properly. I have gone over everything I can think of, and I
can't find the problem. I am not sure if it is in the query since it works
properly with the Form, but, I can't find anything in the report form that
might be causing the problem. The SQL for the query the form and report are
based upon is as follows;

SELECT tblLaborCosts.EmpName, tblLaborCosts.EmpID, tblLaborCosts.JobDate,
tblLaborCosts.ContractorName, tblLaborCosts.JobType,
tblLaborCosts.JobLocation, tblLaborCosts.Manhours, tblLaborCosts.HourlyRate,
Sum([Manhours]*[HourlyRate]) AS TotalCost
FROM tblLaborCosts
GROUP BY tblLaborCosts.EmpName, tblLaborCosts.EmpID, tblLaborCosts.JobDate,
tblLaborCosts.ContractorName, tblLaborCosts.JobType,
tblLaborCosts.JobLocation, tblLaborCosts.Manhours, tblLaborCosts.HourlyRate
HAVING (((tblLaborCosts.JobDate) Between [Forms]![frmLaborFilter]![Text62]
And [Forms]![frmLaborFilter]![Text64])) OR
((([Forms]![frmLaborFilter]![Text62]) Is Null)) OR
((([Forms]![frmLaborFilter]![Text64]) Is Null));

Is three something I set up wrong that prevents the report from sorting in
accordance with the form data?

Cheers, and Happy Holidays!
Jan

Jan,
The sort order of a form, table, or query is irrelevant to the sort
order of a report.
To sort a report, in Report Design View,
click View + Sorting and Grouping.
Set the sort order there.

I am afraid that I may be using the wrong word, sort, which is confusing
what I am really trying to explain is the problem. The problem is not how
to sort the records on the report form itself. This I know how to do using
the appropriate Sort features on the report form. ;-)

The problem, and obviously I am not using the right term, is that when I
enter/select specific information using controls on a master or filter form
to call up a specific group of records from a table to print in a report,
this specific group of records is not being brought up in the report, as all
the records in the table are being displayed in the report preview, and
printed out, not just the records I am calling up from the control form.
There is also a related viewing form that displays the exact records that
are called from the control form, and these, and only these, should be
printed out in the report.

I am trying to troubleshoot why just the specific group of records selected
from the master form will not come up in the report, instead of all the
records. I thought perhaps there was something in the query that would
prevent the records from being separated out and called into the report
properly as they are in the viewing form, thus, allowing all the records to
be displayed and printed out.

I am truly sorry, there just seems to be such a variety of terms folks use
for various objects, controls and functions that I get confused as to which
ones are the 'correct' terms to properly explain what I mean. <g>

If I called a certain group of records up into the viewing form, and I want
a report to print this same exact set of records, then I understood that you
could then click a command button so coded, or a macro, to print the report,
and the report should print out the exact same records, only in a report
format. Is this correct? If not, then I would truly appreciate it if you
would explain to me where I have made my mistake in my understanding of the
process.

I just thought perhaps there was something in the query that was not right
somehow that was confusing or preventing the records to be displayed or
printed properly in the report, but, did not affect the viewing form. As
there's no error message or anything to let me know what the problem is, or
where, I have no idea what it might be, what to look for, or where, to try
to troubleshoot the problem. I have redone all three forms and the query
twice, yet the problem persists. I have imported all the forms and related
items to a new database to test and have done a compact and repair. Nothing
seems to cast any light on what is wrong, or where. The report just won't
work properly.

Sorry for the confusion, but I really am trying. To explain that is...not
confuse....<g>

Happy Holidays!
Jan