Query for report won't sort

  • Thread starter Thread starter Jan Il
  • Start date Start date
J

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 :)
 
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.
 
Hi Joe!

Joe Fallon said:
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.
Click to expand...

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



Jan Il said:
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 :)
Click to expand...
Click to expand...
 
Jan said:
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 :)
Click to expand...

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,

If the form and report are based on the same query, then I would
expect the report to only print those records according to the
criteria entered. I don't see any indication in the query you posted
that you are trying to select records according to Contractor, and it
seems strange that the Manhours and HourlyRate fields are included in
the Group By clause... but that's another issue. Do you by chance
have any code which "clears" the entries on the "filter form" from the
Text62 and/or Text64 textboxes? Alternatively, is the focus on the
form still on one of these criteria textboxes at the time that the
report is printed?

- Steve Schapel, Microsoft Access MVP
 
Hi Fred!

fredg said:
Jan said:
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 :)
Click to expand...

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.
Click to expand...

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 :)
 
Hi Steve!

Steve Schapel said:
Jan,

If the form and report are based on the same query, then I would
expect the report to only print those records according to the
criteria entered. I don't see any indication in the query you posted
that you are trying to select records according to Contractor, and it
seems strange that the Manhours and HourlyRate fields are included in
the Group By clause... but that's another issue. Do you by chance
have any code which "clears" the entries on the "filter form" from the
Text62 and/or Text64 textboxes? Alternatively, is the focus on the
form still on one of these criteria textboxes at the time that the
report is printed?
Click to expand...

There are four combo boxes on the master/control form, EmpName,
ContratorName, JobType and JobLocation. There are also tow text boxes one
for the From Date (Text62) and To Date (Text64). If I need to call up
records for a specific Contractor (Mass Elec) for a specific date period, I
select Mass Elec from the ContractorName combo box, then enter the date
period I want in the From-To date text boxes (07/01/03 - 12/31/03) and then
click a command button to open the viewing form. The code behind the command
button is:

*********************Begin code**********************
Private Sub cmdExpResearch_Click()
On Error GoTo Err_cmdExpResearch_Click

Dim strWhere As String

Dim stDocName As String
Dim stLinkCriteria As String

strWhere = ""

If IsNull(Me!cmbJobLocation) = False Then
strWhere = "[JobLocation]=" & "'" & Me![cmbJobLocation] & "'"
End If

Me![cmbJobLocation] = Null

If IsNull(Me!cmbJobType) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[JobType]=" & "'" & Me![cmbJobType] & "'"
End If

Me![cmbJobType] = Null

If IsNull(Me!cmbContractorName) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[ContractorName]=" & "'" & Me![cmbContractorName]
& "'"
End If

Me![cmbContractorName] = Null

' do empname

If IsNull(Me!cmbEmpName) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[EmpName]=" & "'" & Me![cmbEmpName] & "'"
End If

Me![cmbEmpName] = Null

stDocName = "frmLaborCosts"

stLinkCriteria = strWhere
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdExpResearch_Click:
Exit Sub

Err_cmdExpResearch_Click:
MsgBox Err.Description
Resume Exit_cmdExpResearch_Click

End Sub
****************End Code*******************

THis opens the viewing form with all the records for Mass Elec from July 1,
03 to Dec 31, 03 displayed. This is as it should be. But, when I click the
command button on the form to preview the report all the records of the
table are displayed, not just those in the viewing from for Mass Elec from
7/01/03 to 12/31/03. The code behind the command button to preview the
report is;

*********Begin Code*****************
Private Sub cmdRptPreview_Click()
On Error GoTo Err_cmdPrtPreview_Click

Dim stDocName As String

stDocName = "rptLaborCosts"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdRptPreview_Click:
Exit Sub

Err_cmdPrtPreview_Click:
MsgBox Err.Description
Resume Exit_cmdRptPreview_Click

End Sub
******************End code******************

The only code I am aware of that might be 'clearing' code is in the open
viewing form command button code, and the Is Null criteria in the two rows
for the related date grids in the query.

As for the focus on one of the date text boxes on the form as you mentioned,
I don't think so, the report is printed form the viewing from that is opened
via the command button to view the records. However, there are two controls
on the form header, one each for the From and To dates, to indicate the date
period the records are for, as well as two text boxes to display the total
Man-hours and Rates, which calculate the totals from the respective fields
in the form. This is to allow the management staff to see how many total
man-hours and how much labor expense has been expended in a specific period
or a specific Contractor when a printed report is not necessary at the
moment.

However, there are times when there are no date frames needed, and all the
records are to be printed out, so there are no date entries. I have looked
at this carefully, to see if the presence of dates, or not, have any impact
on how the report works, but, there is no difference, all the records still
show up regardless of criteria.

Thank you for your time and help, Steve, I really appreciate it.

Happy Holidays to you and your family!
Jan :)
 
Jan,

Here's the situation as I see it, without going into specifics... the
code you showed for cmdExpResearch_Click is going through a process of
defining the records to be shown on the frmLaborCosts form, by
progressively building a WHERE clause based on the various criteria
entered. Fair enough. And then, the entered criteria are being
"cleared" from the controls into which hey have been entered, for
example the lines in your code such as...
Me![cmbJobLocation] = Null
OK, after this you want to print a report, rptLaborCosts, with the
same set of records as you have displayed on frmLaborCosts. Am I
right? Well,... what is the recordsource of the report? How can the
report show the same records as the form, if you don't use the same
process of defining the report's recordsource as you did in defining
the form's recordsource? And how could you do this anyway, even if
you wanted to, because the criteria, which the record selection
depends on, have been cleared? Hope this makes sense to you.

- Steve Schapel, Microsoft Access MVP


Hi Steve!
Click to expand...
There are four combo boxes on the master/control form, EmpName,
ContratorName, JobType and JobLocation. There are also tow text boxes one
for the From Date (Text62) and To Date (Text64). If I need to call up
records for a specific Contractor (Mass Elec) for a specific date period, I
select Mass Elec from the ContractorName combo box, then enter the date
period I want in the From-To date text boxes (07/01/03 - 12/31/03) and then
click a command button to open the viewing form. The code behind the command
button is:

*********************Begin code**********************
Private Sub cmdExpResearch_Click()
On Error GoTo Err_cmdExpResearch_Click

Dim strWhere As String

Dim stDocName As String
Dim stLinkCriteria As String

strWhere = ""

If IsNull(Me!cmbJobLocation) = False Then
strWhere = "[JobLocation]=" & "'" & Me![cmbJobLocation] & "'"
End If

Me![cmbJobLocation] = Null

If IsNull(Me!cmbJobType) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[JobType]=" & "'" & Me![cmbJobType] & "'"
End If

Me![cmbJobType] = Null

If IsNull(Me!cmbContractorName) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[ContractorName]=" & "'" & Me![cmbContractorName]
& "'"
End If

Me![cmbContractorName] = Null

' do empname

If IsNull(Me!cmbEmpName) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[EmpName]=" & "'" & Me![cmbEmpName] & "'"
End If

Me![cmbEmpName] = Null

stDocName = "frmLaborCosts"

stLinkCriteria = strWhere
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdExpResearch_Click:
Exit Sub

Err_cmdExpResearch_Click:
MsgBox Err.Description
Resume Exit_cmdExpResearch_Click

End Sub
****************End Code*******************

THis opens the viewing form with all the records for Mass Elec from July 1,
03 to Dec 31, 03 displayed. This is as it should be. But, when I click the
command button on the form to preview the report all the records of the
table are displayed, not just those in the viewing from for Mass Elec from
7/01/03 to 12/31/03. The code behind the command button to preview the
report is;

*********Begin Code*****************
Private Sub cmdRptPreview_Click()
On Error GoTo Err_cmdPrtPreview_Click

Dim stDocName As String

stDocName = "rptLaborCosts"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdRptPreview_Click:
Exit Sub

Err_cmdPrtPreview_Click:
MsgBox Err.Description
Resume Exit_cmdRptPreview_Click

End Sub
******************End code******************

The only code I am aware of that might be 'clearing' code is in the open
viewing form command button code, and the Is Null criteria in the two rows
for the related date grids in the query.

As for the focus on one of the date text boxes on the form as you mentioned,
I don't think so, the report is printed form the viewing from that is opened
via the command button to view the records. However, there are two controls
on the form header, one each for the From and To dates, to indicate the date
period the records are for, as well as two text boxes to display the total
Man-hours and Rates, which calculate the totals from the respective fields
in the form. This is to allow the management staff to see how many total
man-hours and how much labor expense has been expended in a specific period
or a specific Contractor when a printed report is not necessary at the
moment.

However, there are times when there are no date frames needed, and all the
records are to be printed out, so there are no date entries. I have looked
at this carefully, to see if the presence of dates, or not, have any impact
on how the report works, but, there is no difference, all the records still
show up regardless of criteria.

Thank you for your time and help, Steve, I really appreciate it.

Happy Holidays to you and your family!
Jan :)
Click to expand...
 
Hi Steve!
Jan,

Here's the situation as I see it, without going into specifics... the
code you showed for cmdExpResearch_Click is going through a process of
defining the records to be shown on the frmLaborCosts form, by
progressively building a WHERE clause based on the various criteria
entered. Fair enough. And then, the entered criteria are being
"cleared" from the controls into which they have been entered, for
example the lines in your code such as...
Me![cmbJobLocation] = Null
OK, after this you want to print a report, rptLaborCosts, with the
same set of records as you have displayed on frmLaborCosts. Am I
right?
Click to expand...

Yes, this is correct.
Well,... what is the recordsource of the report? How can the
report show the same records as the form, if you don't use the same
process of defining the report's recordsource as you did in defining
the form's recordsource? And how could you do this anyway, even if
you wanted to, because the criteria, which the record selection
depends on, have been cleared? Hope this makes sense to you.
Click to expand...

'k....I think I see what is happening here now. The code for the button on
the frmLaborFilter is defining the records to be called into the
frmLabotCosts based upon the criteria entered in the frmLaborFilter
controls, as it should. But, if I understand here, once the frmLaborCosts
is opened, all the criteria in the controls on the frmLaborFilter is cleared
by the code in the command button on the frmLaborFilter controls because of
the =Null. Is this correct? Thus, there's actually no criteria remaining in
the frmLaborFilter controls to define the same set of records to be called
into the rptLaborCosts as in the frmLaborCosts at that time. That is why the
report displays and prints all the records. Is this correct?

Thus....I have in fact managed to properly define the target, bring it into
focus, but end up shooting myself in the foot. Is this correct? <g>

Thank you for your time and help, I appreciate it very much.

Merry Christmas!
Jan :)
Hi Steve!
Click to expand...
There are four combo boxes on the master/control form, EmpName,
ContratorName, JobType and JobLocation. There are also tow text boxes one
for the From Date (Text62) and To Date (Text64). If I need to call up
records for a specific Contractor (Mass Elec) for a specific date period, I
select Mass Elec from the ContractorName combo box, then enter the date
period I want in the From-To date text boxes (07/01/03 - 12/31/03) and then
click a command button to open the viewing form. The code behind the command
button is:

*********************Begin code**********************
Private Sub cmdExpResearch_Click()
On Error GoTo Err_cmdExpResearch_Click

Dim strWhere As String

Dim stDocName As String
Dim stLinkCriteria As String

strWhere = ""

If IsNull(Me!cmbJobLocation) = False Then
strWhere = "[JobLocation]=" & "'" & Me![cmbJobLocation] & "'"
End If

Me![cmbJobLocation] = Null

If IsNull(Me!cmbJobType) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[JobType]=" & "'" & Me![cmbJobType] & "'"
End If

Me![cmbJobType] = Null

If IsNull(Me!cmbContractorName) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[ContractorName]=" & "'" & Me![cmbContractorName]
& "'"
End If

Me![cmbContractorName] = Null

' do empname

If IsNull(Me!cmbEmpName) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[EmpName]=" & "'" & Me![cmbEmpName] & "'"
End If

Me![cmbEmpName] = Null

stDocName = "frmLaborCosts"

stLinkCriteria = strWhere
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdExpResearch_Click:
Exit Sub

Err_cmdExpResearch_Click:
MsgBox Err.Description
Resume Exit_cmdExpResearch_Click

End Sub
****************End Code*******************

THis opens the viewing form with all the records for Mass Elec from July 1,
03 to Dec 31, 03 displayed. This is as it should be. But, when I click the
command button on the form to preview the report all the records of the
table are displayed, not just those in the viewing from for Mass Elec from
7/01/03 to 12/31/03. The code behind the command button to preview the
report is;

*********Begin Code*****************
Private Sub cmdRptPreview_Click()
On Error GoTo Err_cmdPrtPreview_Click

Dim stDocName As String

stDocName = "rptLaborCosts"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdRptPreview_Click:
Exit Sub

Err_cmdPrtPreview_Click:
MsgBox Err.Description
Resume Exit_cmdRptPreview_Click

End Sub
******************End code******************

The only code I am aware of that might be 'clearing' code is in the open
viewing form command button code, and the Is Null criteria in the two rows
for the related date grids in the query.

As for the focus on one of the date text boxes on the form as you mentioned,
I don't think so, the report is printed form the viewing from that is opened
via the command button to view the records. However, there are two controls
on the form header, one each for the From and To dates, to indicate the date
period the records are for, as well as two text boxes to display the total
Man-hours and Rates, which calculate the totals from the respective fields
in the form. This is to allow the management staff to see how many total
man-hours and how much labor expense has been expended in a specific period
or a specific Contractor when a printed report is not necessary at the
moment.

However, there are times when there are no date frames needed, and all the
records are to be printed out, so there are no date entries. I have looked
at this carefully, to see if the presence of dates, or not, have any impact
on how the report works, but, there is no difference, all the records still
show up regardless of criteria.

Thank you for your time and help, Steve, I really appreciate it.

Happy Holidays to you and your family!
Jan :)
Click to expand...
Click to expand...
 
Jan,

Thus....I have in fact managed to properly define the target, bring it into
focus, but end up shooting myself in the foot. Is this correct? <g>
Click to expand...

Yep, this about sums it up! Best wishes.

- Steve Schapel, Microsoft Access MVP
 
Back
Top