Repost: Please Help

[Tia]

**I've copied all questions and suggestions thus far, but I'm still not
getting desireable results***Any suggestions would be greatly appreciated!
********

Thanks again for your help....I tried putting the filter as a "where"
filtering string on the DoCmd.OpenReport method. Instead of filtering my
reports when I clicked on the button, it opened all reports. Here's the code
I used:

Bart_Week_S1_Click
'
'------------------------------------------------------------
Private Sub Bart_Week_S1_Click()
On Error GoTo Bart_Week_S1_Click_Err

DoCmd.OpenReport "ServiceReport", acViewPreview, "((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=0)) OR ((([System Information].Employee)='Bart')
AND (([System Information].Service)<>'W') AND (([System
Information].Week)=1)) OR ((([System Information].Employee)='Bart') AND
(([System Information].Service)<>'W') AND (([System Information].Week)=7))",
"", acNormal
DoCmd.OutputTo acReport, "ServiceReport", "RichTextFormat(*.rtf)",
"Service Report.rtf", False, "", 0


Bart_Week_S1_Click_Exit:
Exit Sub

Bart_Week_S1_Click_Err:
MsgBox Error$
Resume Bart_Week_S1_Click_Exit

End Sub '------------------------------------------------------------

Am I missing something or doing something wrong????

I, also, tried chagning the filter property of the report and main report
and it didn't seem to change anything.

Thanks again for your help!!

The first query you posted is being used as the filtering query for the
report's recordsource query, right? But it doesn't include all the fields
from the report's recordsource query, so I'm not sure how it should be able
to work correctly.

I admit to you that I do not use filtering queries for my reports,
preferring instead to use the WHERE argument in the DoCmd.OpenReport method
(or use the report's Filter property to have a "WHERE" filtering string in
it). I find this much easier and more understandable.

Let's try something with your report setup and see what happens.

Set the Filter property of both the main report and the subreport to this
WHERE expression (do this in design view of the report and subreport) (all
one long line, even though the newsreader likely is wrapping it):

((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=7))


Or, if you're opening the report via DoCmd.OpenReport method (and using the
Bart S1 query as the third argument (Filter) for that action), change that
to this (all one long line, even though the newsreader likely is wrapping
it):

DoCmd.OpenReport "ReportName", , , "((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=0)) OR ((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=1)) OR ((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=7))"


Does the report now work as you expect?

--

Ken Snell
<MS ACCESS MVP>

Name of Query Using: Bart S1

SELECT DISTINCTROW *
FROM [System Information]
WHERE ((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=7));

The query, Bart S1, is used as a filter for the report/subreport. A button
on a form is used to open the report and apply the filter.

Record source for the report and subreport is as follows:

Service Report is based on the Report Query:

SELECT DISTINCT [Customer Information].Customer_ID, [Customer
Information].Customer, [Service Address].Service_Address, [Service
Address].Manager, ServiceRequirements2.Type_of_System, [System
Information].Employee, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, [System
Information].Service, [System Information].Week
FROM ServiceRequirements2 INNER JOIN ([Customer Information] INNER JOIN
([Service Address] INNER JOIN [System Information] ON [Service
Address].Service_Address = [System Information].Service_Address) ON [Customer
Information].Customer_ID = [System Information].Customer_ID) ON
ServiceRequirements2.Type_of_System = [System Information].Type_of_System
GROUP BY [Customer Information].Customer_ID, [Customer
Information].Customer, [Service Address].Service_Address, [Service
Address].Manager, ServiceRequirements2.Type_of_System, [System
Information].Employee, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, [System
Information].Service, [System Information].Week
ORDER BY [Customer Information].Customer, [Service Address].Service_Address,
ServiceRequirements2.Type_of_System;


Service Subreport is based on the Report Query 2:

SELECT DISTINCT [Customer Information].Customer_ID, [System
Information].System, [System Information].Customer_Identification, [System
Information].Service_Address, [System Information].Type_of_System, [System
Information].Week
FROM [Customer Information] INNER JOIN ([System Information] INNER JOIN
[Service Address] ON [System Information].Service_Address = [Service
Address].Service_Address) ON [Customer Information].Customer_ID = [System
Information].Customer_ID
GROUP BY [Customer Information].Customer_ID, [System Information].System,
[System Information].Customer_Identification, [System
Information].Service_Address, [System Information].Type_of_System, [System
Information].Week
ORDER BY [Customer Information].Customer_ID, [System Information].System,
[System Information].Customer_Identification;

Thank you so much for your time and suggestions!!!

Hmmm....

Well, let's start with some basic info. Please post
- the SQL statement of the query that you're using;
- the name of the query that you're using;
- the recordsource of the report and all subreports.


--

Ken Snell
<MS ACCESS MVP>

I've checked the Sorting and Grouping window. Service isnot listed. I,
also, checked the subreport to make sure it wasn't sorting or grouping on
the
service field.

Any other ideas????

Thanks for the suggestion!!

:

While in design view of the report, go to View | Sorting and Grouping.

Likely you'll find the service field listed there as the way the report
is
to group the data. Delete the field from that list and close the window.

--

Ken Snell
<MS ACCESS MVP>

I have a query that has a service field with A, S, and W. I have
excluded
W
by <>"W" on the design view of the query. When I run the query, it
displays
all records that do not have a W in service. I then have a report
that
uses
this information, when I open the report it creates a page for both A
and
S,
I want all records to be on the same report and not seperated by A or
S.
However, if the report groups them together than I get duplicates. Is
there
a way to have all records be on one report and not seperated by the
service
field without creating a duplicate??????

I've tried the hiding duplicate function on the report but since all
records
are not exact duplicates, this does not work.The only difference
between
records in the query, is the field service.

Any information that can be provided, will be greatly appreciated.
I've
tried everything that I could think of....

Thanks!

 

[Steve Schapel]

Tia,

If you are attempting to use the Where Condition argument, then the
syntax of your code is not correct, in that there is a comma missing.
Try it like this...

DoCmd.OpenReport "ServiceReport", acViewPreview, , "((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W')
AND (([System Information].Week)=0)) OR ((([System
Information].Employee)='Bart')
AND (([System Information].Service)<>'W') AND (([System
Information].Week)=1)) OR ((([System Information].Employee)='Bart') AND
(([System Information].Service)<>'W') AND (([System Information].Week)=7))"

Or, to simplify....
DoCmd.OpenReport "ServiceReport", acViewPreview, , "[System
Information].Employee='Bart' AND [System Information].Service<>'W' AND
[System Information].Week In(0,1,7)"

 

[Tia]

Steve,
I tried your statement and it doesn't sort records according to the Service
or Week. It pulls all records regardless of the filter. I've attached it so
you could see what I entered - Did I enter it wrong? Thanks for your help!

Bart_Week_S1_Click
'
'------------------------------------------------------------
Private Sub Bart_Week_S1_Click()
On Error GoTo Bart_Week_S1_Click_Err

DoCmd.OpenReport "ServiceReport", acViewPreview, , "[System
Information].Employee='Bart' AND [System Information].Service<>'W' AND
[System Information].Week In(0,1,7)"

Bart_Week_S1_Click_Exit:
Exit Sub

Bart_Week_S1_Click_Err:
MsgBox Error$
Resume Bart_Week_S1_Click_Exit

End Sub '------------------------------------------------------------

Tia,

If you are attempting to use the Where Condition argument, then the
syntax of your code is not correct, in that there is a comma missing.
Try it like this...

DoCmd.OpenReport "ServiceReport", acViewPreview, , "((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W')
AND (([System Information].Week)=0)) OR ((([System
Information].Employee)='Bart')
AND (([System Information].Service)<>'W') AND (([System
Information].Week)=1)) OR ((([System Information].Employee)='Bart') AND
(([System Information].Service)<>'W') AND (([System Information].Week)=7))"

Or, to simplify....
DoCmd.OpenReport "ServiceReport", acViewPreview, , "[System
Information].Employee='Bart' AND [System Information].Service<>'W' AND
[System Information].Week In(0,1,7)"

--
Steve Schapel, Microsoft Access MVP

**I've copied all questions and suggestions thus far, but I'm still not
getting desireable results***Any suggestions would be greatly appreciated!
********

Thanks again for your help....I tried putting the filter as a "where"
filtering string on the DoCmd.OpenReport method. Instead of filtering my
reports when I clicked on the button, it opened all reports. Here's the code
I used:

Bart_Week_S1_Click
'
'------------------------------------------------------------
Private Sub Bart_Week_S1_Click()
On Error GoTo Bart_Week_S1_Click_Err

DoCmd.OpenReport "ServiceReport", acViewPreview, "((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=0)) OR ((([System Information].Employee)='Bart')
AND (([System Information].Service)<>'W') AND (([System
Information].Week)=1)) OR ((([System Information].Employee)='Bart') AND
(([System Information].Service)<>'W') AND (([System Information].Week)=7))",
"", acNormal
DoCmd.OutputTo acReport, "ServiceReport", "RichTextFormat(*.rtf)",
"Service Report.rtf", False, "", 0

 

[Steve Schapel]

Tia,

You mention sorting records. Sorting doesn't come into it. That needs
to be done within the design of the report.

Ok, so do you mean to tell me that:
1. The RecordSource of the Report includes fields named [System
Information].[Service] and [System Information].[Week]
2. When printed, the report includes data where [System
Information].Service is "W"
3. When printed, the report includes data where [System
Information].Week is something other than 0 or 1 or 7

Does the query that the report is based on include Service or Week
fields from more than one table, i.e. in addition to the [System
Information] table? If not, try it without the table/query references...
DoCmd.OpenReport "ServiceReport", acViewPreview, , "Employee='Bart' AND
Service<>'W' AND [Week] In(0,1,7)"

 

[Tia]

Steve,
Thanks for your help. It still isn't working appropriately....I'm almost
wondering if it was something I did when I created the query....Would it be
better if I started over and what things would I need to do to get the
desired result???

To answer your questions:
1. Yes, the RecordSource includes fileds named [System
Information].[Service} and [System Information].[Week]/

2. When printed, the report includes everything including any record with a
"W" in the Service field

3. When printed, the report includes all weeks not just those with a 0,1,7
in the Week field.

I tried changing the command per your suggestion, but I got the same exact
results. Could I be having problems because I apply different "filters" to
the same report/subreport through the use of a form with buttons for each
week of the month? I'm just trying to think of something I may have done in
setting up the Form that could be inhibiting the ability of the filter to
work right with the report....

If you have anymore ideas or suggestions, I would appreciate them because
I'm at a complete loss. Thanks for your help thus far!

Tia,

You mention sorting records. Sorting doesn't come into it. That needs
to be done within the design of the report.

Ok, so do you mean to tell me that:
1. The RecordSource of the Report includes fields named [System
Information].[Service] and [System Information].[Week]
2. When printed, the report includes data where [System
Information].Service is "W"
3. When printed, the report includes data where [System
Information].Week is something other than 0 or 1 or 7

Does the query that the report is based on include Service or Week
fields from more than one table, i.e. in addition to the [System
Information] table? If not, try it without the table/query references...
DoCmd.OpenReport "ServiceReport", acViewPreview, , "Employee='Bart' AND
Service<>'W' AND [Week] In(0,1,7)"

--
Steve Schapel, Microsoft Access MVP

Steve,
I tried your statement and it doesn't sort records according to the Service
or Week. It pulls all records regardless of the filter. I've attached it so
you could see what I entered - Did I enter it wrong? Thanks for your help!

Bart_Week_S1_Click
'
'------------------------------------------------------------
Private Sub Bart_Week_S1_Click()
On Error GoTo Bart_Week_S1_Click_Err

DoCmd.OpenReport "ServiceReport", acViewPreview, , "[System
Information].Employee='Bart' AND [System Information].Service<>'W' AND
[System Information].Week In(0,1,7)"

Bart_Week_S1_Click_Exit:
Exit Sub

Bart_Week_S1_Click_Err:
MsgBox Error$
Resume Bart_Week_S1_Click_Exit

End Sub '------------------------------------------------------------

 

[Steve Schapel]

Tia,

Scratching my brain and trying to think of circumstances which might
cause this unusual behaviour. Only one I can think of right now is: Do
you have any macro or code on the Open event of the Report itself,
setting the report's RecordSource?

 

[Tia]

I can't find any macro or code on the Open event of the report. However, a
record source has been defined in the properties dialogue box.

Tia,

Scratching my brain and trying to think of circumstances which might
cause this unusual behaviour. Only one I can think of right now is: Do
you have any macro or code on the Open event of the Report itself,
setting the report's RecordSource?

--
Steve Schapel, Microsoft Access MVP

Steve,
Thanks for your help. It still isn't working appropriately....I'm almost
wondering if it was something I did when I created the query....Would it be
better if I started over and what things would I need to do to get the
desired result???

To answer your questions:
1. Yes, the RecordSource includes fileds named [System
Information].[Service} and [System Information].[Week]/

2. When printed, the report includes everything including any record with a
"W" in the Service field

3. When printed, the report includes all weeks not just those with a 0,1,7
in the Week field.

I tried changing the command per your suggestion, but I got the same exact
results. Could I be having problems because I apply different "filters" to
the same report/subreport through the use of a form with buttons for each
week of the month? I'm just trying to think of something I may have done in
setting up the Form that could be inhibiting the ability of the filter to
work right with the report....

If you have anymore ideas or suggestions, I would appreciate them because
I'm at a complete loss. Thanks for your help thus far!