Reports (All Based on the Same Query) Filtered on Opening

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

I have a report rptEmployees, based on qryEmployees.
Is there a method by which I can create a series of reports, based on that
query, but on opening, will only show records (filtered?) based on selected
criteria:

(a) Those who have telephone numbers
(bJ) Those who have "external" as status
(c) Those who have "internal" as status

I am under the assumption that all 3 reports can be based on a single query,
but when opened, display only those records matching the criteria on a single
field? Is this correct? Thanks, in advance.
 
Robert said:
I have a report rptEmployees, based on qryEmployees.
Is there a method by which I can create a series of reports, based on that
query, but on opening, will only show records (filtered?) based on
selected
criteria:

(a) Those who have telephone numbers
(bJ) Those who have "external" as status
(c) Those who have "internal" as status

I am under the assumption that all 3 reports can be based on a single
query,
but when opened, display only those records matching the criteria on a
single
field? Is this correct? Thanks, in advance.
Click to expand...


You could easily open the same report, using code or a macro, filtered in
different ways. Here's a VBA code example:

' Print employees with phone numbers. Pass an argument
' that the report can use to set captions as needed.
DoCmd.OpenReport "rptEmployees", _
WhereCondition:="PhoneNumber Is Not Null", _
OpenArgs:="Phone Numbers"

' Print external employees. Pass an argument
' that the report can use to set captions as needed.
DoCmd.OpenReport "rptEmployees", _
WhereCondition:="Status='external'", _
OpenArgs:="External"

' Print internal employees. Pass an argument
' that the report can use to set captions as needed.
DoCmd.OpenReport "rptEmployees", _
WhereCondition:="Status='internal'", _
OpenArgs:="External"

Of course, I'm making assumptions about the names and data types of the
fields involved. Note the use of the OpenArgs argument to pass a tag to the
report so that code in the report's Open event can set headers depending on
what the report is going to show.
 
I have a report rptEmployees, based on qryEmployees.
Is there a method by which I can create a series of reports, based on that
query, but on opening, will only show records (filtered?) based on selected
criteria:

(a) Those who have telephone numbers
(bJ) Those who have "external" as status
(c) Those who have "internal" as status

I am under the assumption that all 3 reports can be based on a single query,
but when opened, display only those records matching the criteria on a single
field? Is this correct? Thanks, in advance.
Click to expand...

Simplest way is to open the report from a form.
You can create a new form or add this to an existing form.

Add an OptionGroup control to the form.
Add 3 radio buttons to the OptionGroup.
Label captions are:
Phone Numbers (Value of 1)
External (Value of 2)
Internal (Value of 3)
All (Value of 4)

Add a Command button to the form.
Code it's Click event:

Dim strWhere as String
If Me![OptionGroupName] = 1 then
strWhere = "[PhoneNumber] is not null"
ElseIf Me![OptionGroupName] = 2 then
strWhere = "[Status] = 'External'"
ElseIf Me![OptionGroupName] = 3 then
strWhere = "[Status] = 'Internal'"
Else
strWhere = ""
End If
Docmd.OpenReport "ReportName", acViewPreview, , strWhere

Change [OptionGroupName] to whatever the actual name of the Option
Group is.

If no option is selected (or All is selected), the report will display
all records.
 
Thank you, Fred (and Dirk). I tried your solution and had to fix a couple of
syntax errors that prevented Telephone and All from displaying properly. I
still have a problem with Status. No records are displayed in either case.

Must the field Status be present in the report in order to display the
records by criteria filter?

Thanks again.


fredg said:
I have a report rptEmployees, based on qryEmployees.
Is there a method by which I can create a series of reports, based on that
query, but on opening, will only show records (filtered?) based on selected
criteria:

(a) Those who have telephone numbers
(bJ) Those who have "external" as status
(c) Those who have "internal" as status

I am under the assumption that all 3 reports can be based on a single query,
but when opened, display only those records matching the criteria on a single
field? Is this correct? Thanks, in advance.
Click to expand...

Simplest way is to open the report from a form.
You can create a new form or add this to an existing form.

Add an OptionGroup control to the form.
Add 3 radio buttons to the OptionGroup.
Label captions are:
Phone Numbers (Value of 1)
External (Value of 2)
Internal (Value of 3)
All (Value of 4)

Add a Command button to the form.
Code it's Click event:

Dim strWhere as String
If Me![OptionGroupName] = 1 then
strWhere = "[PhoneNumber] is not null"
ElseIf Me![OptionGroupName] = 2 then
strWhere = "[Status] = 'External'"
ElseIf Me![OptionGroupName] = 3 then
strWhere = "[Status] = 'Internal'"
Else
strWhere = ""
End If
Docmd.OpenReport "ReportName", acViewPreview, , strWhere

Change [OptionGroupName] to whatever the actual name of the Option
Group is.

If no option is selected (or All is selected), the report will display
all records.
Click to expand...
 
I get a type mismatch 3464 error. The values stored in the Status field
(actually, it's called InternalExternal) is a number representing the text
field "Internal" or "External".



fredg said:
I have a report rptEmployees, based on qryEmployees.
Is there a method by which I can create a series of reports, based on that
query, but on opening, will only show records (filtered?) based on selected
criteria:

(a) Those who have telephone numbers
(bJ) Those who have "external" as status
(c) Those who have "internal" as status

I am under the assumption that all 3 reports can be based on a single query,
but when opened, display only those records matching the criteria on a single
field? Is this correct? Thanks, in advance.
Click to expand...

Simplest way is to open the report from a form.
You can create a new form or add this to an existing form.

Add an OptionGroup control to the form.
Add 3 radio buttons to the OptionGroup.
Label captions are:
Phone Numbers (Value of 1)
External (Value of 2)
Internal (Value of 3)
All (Value of 4)

Add a Command button to the form.
Code it's Click event:

Dim strWhere as String
If Me![OptionGroupName] = 1 then
strWhere = "[PhoneNumber] is not null"
ElseIf Me![OptionGroupName] = 2 then
strWhere = "[Status] = 'External'"
ElseIf Me![OptionGroupName] = 3 then
strWhere = "[Status] = 'Internal'"
Else
strWhere = ""
End If
Docmd.OpenReport "ReportName", acViewPreview, , strWhere

Change [OptionGroupName] to whatever the actual name of the Option
Group is.

If no option is selected (or All is selected), the report will display
all records.
Click to expand...
 
I get a type mismatch 3464 error. The values stored in the Status field
(actually, it's called InternalExternal) is a number representing the text
field "Internal" or "External".

fredg said:
I have a report rptEmployees, based on qryEmployees.
Is there a method by which I can create a series of reports, based on that
query, but on opening, will only show records (filtered?) based on selected
criteria:

(a) Those who have telephone numbers
(bJ) Those who have "external" as status
(c) Those who have "internal" as status

I am under the assumption that all 3 reports can be based on a single query,
but when opened, display only those records matching the criteria on a single
field? Is this correct? Thanks, in advance.
Click to expand...

Simplest way is to open the report from a form.
You can create a new form or add this to an existing form.

Add an OptionGroup control to the form.
Add 3 radio buttons to the OptionGroup.
Label captions are:
Phone Numbers (Value of 1)
External (Value of 2)
Internal (Value of 3)
All (Value of 4)

Add a Command button to the form.
Code it's Click event:

Dim strWhere as String
If Me![OptionGroupName] = 1 then
strWhere = "[PhoneNumber] is not null"
ElseIf Me![OptionGroupName] = 2 then
strWhere = "[Status] = 'External'"
ElseIf Me![OptionGroupName] = 3 then
strWhere = "[Status] = 'Internal'"
Else
strWhere = ""
End If
Docmd.OpenReport "ReportName", acViewPreview, , strWhere

Change [OptionGroupName] to whatever the actual name of the Option
Group is.

If no option is selected (or All is selected), the report will display
all records.
Click to expand...
Click to expand...

We can only answer as you have posted.
You posted "text field "Internal" or "External".
So I responded as though the value in the [Status] field must be text.
It seems [Status] not text it's a Number datatype, so....

ElseIf Me![OptionGroupName] = 2 then
strWhere = "[InternalExternal] = 1"
ElseIf Me![OptionGroupName] = 3 then
strWhere = "[InternalExternal] = 2"

The above assumes the actual value stored in the table field
[InternalExternal] is either a 1 or a 2.
Adjust the number value accordingly.
Note that there are no single quotes used.
 
fredg said:
I get a type mismatch 3464 error. The values stored in the Status field
(actually, it's called InternalExternal) is a number representing the text
field "Internal" or "External".

fredg said:
On Thu, 14 Aug 2008 08:09:01 -0700, Robert wrote:

I have a report rptEmployees, based on qryEmployees.
Is there a method by which I can create a series of reports, based on that
query, but on opening, will only show records (filtered?) based on selected
criteria:

(a) Those who have telephone numbers
(bJ) Those who have "external" as status
(c) Those who have "internal" as status

I am under the assumption that all 3 reports can be based on a single query,
but when opened, display only those records matching the criteria on a single
field? Is this correct? Thanks, in advance.

Simplest way is to open the report from a form.
You can create a new form or add this to an existing form.

Add an OptionGroup control to the form.
Add 3 radio buttons to the OptionGroup.
Label captions are:
Phone Numbers (Value of 1)
External (Value of 2)
Internal (Value of 3)
All (Value of 4)

Add a Command button to the form.
Code it's Click event:

Dim strWhere as String
If Me![OptionGroupName] = 1 then
strWhere = "[PhoneNumber] is not null"
ElseIf Me![OptionGroupName] = 2 then
strWhere = "[Status] = 'External'"
ElseIf Me![OptionGroupName] = 3 then
strWhere = "[Status] = 'Internal'"
Else
strWhere = ""
End If
Docmd.OpenReport "ReportName", acViewPreview, , strWhere

Change [OptionGroupName] to whatever the actual name of the Option
Group is.

If no option is selected (or All is selected), the report will display
all records.
Click to expand...
Click to expand...

We can only answer as you have posted.
You posted "text field "Internal" or "External".
So I responded as though the value in the [Status] field must be text.
It seems [Status] not text it's a Number datatype, so....

ElseIf Me![OptionGroupName] = 2 then
strWhere = "[InternalExternal] = 1"
ElseIf Me![OptionGroupName] = 3 then
strWhere = "[InternalExternal] = 2"

The above assumes the actual value stored in the table field
[InternalExternal] is either a 1 or a 2.
Adjust the number value accordingly.
Note that there are no single quotes used.
Click to expand...
Yes, and of course it works perfectly, as described. I do apologize for the
misdirection on the data values. Thanks again.
 
Back
Top