Report filter

[Jacques Latoison]

Greetings,
I need to adjust a report so that it filters records as opposed to the
query doing the filtering.
I can't do this in the query because elsewhere on the report it uses all the
data from the query, but the main body needs to list all the records except
for one, by criteria from one of the fields.
Is this possible?

Thanks beforehand.

Jacques

 

[Golfinray]

I use a table with just the name of the item I want to filter on, like in
some of my reports I want to filter on AREA. I have a table with the area
names, like area A, area B, and so forth. I then create a form, put a combo
box on the form (use the wizard to do that for you) and select the table you
created as the source of the combo box. Then right click on the combo and
select events. Use the onclick event and start the code builder (the little
button out to the right.) Put in this code.
Dim strWhere As String
Dim stDocument As String
stDocument = "06-07 plan review approval letter sent"
strWhere = "1=1 "
If Not IsNull(Me.Combo0) Then
strWhere = strWhere & " And [Area]=""" & _
Me.Combo0 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere
DoCmd.Close acForm, "area form 4", acSaveNo
Change report name to your report name (mine was 06-07 plan review approval
letter sent.) Change your filter to what you need (is used area.) Change to
your combo box number (mine was 0.)

 

[Jacques Latoison]

I'll have to try that, but won't that filter the whole report.
Remember that I said that the rest of the report needs all the data from the
query.

I can't use the table, as the query is needed to group the data.
Its Table - then first query - then second query - then report.

The bottom of the report has sum fields that are summing from the query.
The list above that needs to show all items except for one.

Is that possible?

I use a table with just the name of the item I want to filter on, like in
some of my reports I want to filter on AREA. I have a table with the area
names, like area A, area B, and so forth. I then create a form, put a
combo
box on the form (use the wizard to do that for you) and select the table
you
created as the source of the combo box. Then right click on the combo and
select events. Use the onclick event and start the code builder (the
little
button out to the right.) Put in this code.
Dim strWhere As String
Dim stDocument As String
stDocument = "06-07 plan review approval letter sent"
strWhere = "1=1 "
If Not IsNull(Me.Combo0) Then
strWhere = strWhere & " And [Area]=""" & _
Me.Combo0 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere
DoCmd.Close acForm, "area form 4", acSaveNo
Change report name to your report name (mine was 06-07 plan review
approval
letter sent.) Change your filter to what you need (is used area.) Change
to
your combo box number (mine was 0.)

Greetings,
I need to adjust a report so that it filters records as opposed to
the
query doing the filtering.
I can't do this in the query because elsewhere on the report it uses all
the
data from the query, but the main body needs to list all the records
except
for one, by criteria from one of the fields.
Is this possible?

Thanks beforehand.

Jacques

 

[Klatuu]

No, it is not possible. A record is either in the report's recordset or it
is not.

Rather than ask the how, tell us what you want to do and maybe we can offer
a suggestion as to how.

I'll have to try that, but won't that filter the whole report.
Remember that I said that the rest of the report needs all the data from
the query.

I can't use the table, as the query is needed to group the data.
Its Table - then first query - then second query - then report.

The bottom of the report has sum fields that are summing from the query.
The list above that needs to show all items except for one.

Is that possible?

I use a table with just the name of the item I want to filter on, like in
some of my reports I want to filter on AREA. I have a table with the area
names, like area A, area B, and so forth. I then create a form, put a
combo
box on the form (use the wizard to do that for you) and select the table
you
created as the source of the combo box. Then right click on the combo and
select events. Use the onclick event and start the code builder (the
little
button out to the right.) Put in this code.
Dim strWhere As String
Dim stDocument As String
stDocument = "06-07 plan review approval letter sent"
strWhere = "1=1 "
If Not IsNull(Me.Combo0) Then
strWhere = strWhere & " And [Area]=""" & _
Me.Combo0 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere
DoCmd.Close acForm, "area form 4", acSaveNo
Change report name to your report name (mine was 06-07 plan review
approval
letter sent.) Change your filter to what you need (is used area.) Change
to
your combo box number (mine was 0.)

Greetings,
I need to adjust a report so that it filters records as opposed to
the
query doing the filtering.
I can't do this in the query because elsewhere on the report it uses all
the
data from the query, but the main body needs to list all the records
except
for one, by criteria from one of the fields.
Is this possible?

Thanks beforehand.

Jacques

 

[Jacques Latoison]

I have:
Table > query > query > report

In general, the report shows positions and hours worked.

The page footer has:
Totals on one line
Totals for only one of the detail records in the second line
Total of the one line minus the second line

We need the details section to show every position but one, while the page
footer info doesn't change.
The one record we need to NOT show in the details section, is still needed
to help produce the page footer.

Does that make it a bit more clearer?

No, it is not possible. A record is either in the report's recordset or
it is not.

Rather than ask the how, tell us what you want to do and maybe we can
offer a suggestion as to how.

I'll have to try that, but won't that filter the whole report.
Remember that I said that the rest of the report needs all the data from
the query.

I can't use the table, as the query is needed to group the data.
Its Table - then first query - then second query - then report.

The bottom of the report has sum fields that are summing from the query.
The list above that needs to show all items except for one.

Is that possible?

I use a table with just the name of the item I want to filter on, like in
some of my reports I want to filter on AREA. I have a table with the
area
names, like area A, area B, and so forth. I then create a form, put a
combo
box on the form (use the wizard to do that for you) and select the table
you
created as the source of the combo box. Then right click on the combo
and
select events. Use the onclick event and start the code builder (the
little
button out to the right.) Put in this code.
Dim strWhere As String
Dim stDocument As String
stDocument = "06-07 plan review approval letter sent"
strWhere = "1=1 "
If Not IsNull(Me.Combo0) Then
strWhere = strWhere & " And [Area]=""" & _
Me.Combo0 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere
DoCmd.Close acForm, "area form 4", acSaveNo
Change report name to your report name (mine was 06-07 plan review
approval
letter sent.) Change your filter to what you need (is used area.) Change
to
your combo box number (mine was 0.)

:

Greetings,
I need to adjust a report so that it filters records as opposed to
the
query doing the filtering.
I can't do this in the query because elsewhere on the report it uses
all the
data from the query, but the main body needs to list all the records
except
for one, by criteria from one of the fields.
Is this possible?

Thanks beforehand.

Jacques

 

[Klatuu]

You can cause a specific record not to print. Use the control on your
report that is bound to the primary key field or some other unique value
int he record. Here is an example.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Text2 = "Hargis" Then
Cancel = True
End If

End Sub

Also, you will want to set all the controls in the section to Can Grow and
Can Shrink = Yes so it will not leave a blank line.

I have:
Table > query > query > report

In general, the report shows positions and hours worked.

The page footer has:
Totals on one line
Totals for only one of the detail records in the second line
Total of the one line minus the second line

We need the details section to show every position but one, while the page
footer info doesn't change.
The one record we need to NOT show in the details section, is still needed
to help produce the page footer.

Does that make it a bit more clearer?

No, it is not possible. A record is either in the report's recordset or
it is not.

Rather than ask the how, tell us what you want to do and maybe we can
offer a suggestion as to how.

I'll have to try that, but won't that filter the whole report.
Remember that I said that the rest of the report needs all the data from
the query.

I can't use the table, as the query is needed to group the data.
Its Table - then first query - then second query - then report.

The bottom of the report has sum fields that are summing from the query.
The list above that needs to show all items except for one.

Is that possible?


I use a table with just the name of the item I want to filter on, like
in
some of my reports I want to filter on AREA. I have a table with the
area
names, like area A, area B, and so forth. I then create a form, put a
combo
box on the form (use the wizard to do that for you) and select the
table you
created as the source of the combo box. Then right click on the combo
and
select events. Use the onclick event and start the code builder (the
little
button out to the right.) Put in this code.
Dim strWhere As String
Dim stDocument As String
stDocument = "06-07 plan review approval letter sent"
strWhere = "1=1 "
If Not IsNull(Me.Combo0) Then
strWhere = strWhere & " And [Area]=""" & _
Me.Combo0 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere
DoCmd.Close acForm, "area form 4", acSaveNo
Change report name to your report name (mine was 06-07 plan review
approval
letter sent.) Change your filter to what you need (is used area.)
Change to
your combo box number (mine was 0.)

:

Greetings,
I need to adjust a report so that it filters records as opposed to
the
query doing the filtering.
I can't do this in the query because elsewhere on the report it uses
all the
data from the query, but the main body needs to list all the records
except
for one, by criteria from one of the fields.
Is this possible?

Thanks beforehand.

Jacques

 

[Jacques Latoison]

I assume that I'm doing it wrong. It doesn't work at all.

I right click on the field in question, click build event, code builder.
I put in the below and change the Text2 to the name of the field, and
"Hargis" to the data I want filtered, but nothing happens.

Am I missing something?

You can cause a specific record not to print. Use the control on your
report that is bound to the primary key field or some other unique value
int he record. Here is an example.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Text2 = "Hargis" Then
Cancel = True
End If

End Sub

Also, you will want to set all the controls in the section to Can Grow and
Can Shrink = Yes so it will not leave a blank line.

I have:
Table > query > query > report

In general, the report shows positions and hours worked.

The page footer has:
Totals on one line
Totals for only one of the detail records in the second line
Total of the one line minus the second line

We need the details section to show every position but one, while the
page footer info doesn't change.
The one record we need to NOT show in the details section, is still
needed to help produce the page footer.

Does that make it a bit more clearer?

No, it is not possible. A record is either in the report's recordset or
it is not.

Rather than ask the how, tell us what you want to do and maybe we can
offer a suggestion as to how.

I'll have to try that, but won't that filter the whole report.
Remember that I said that the rest of the report needs all the data
from the query.

I can't use the table, as the query is needed to group the data.
Its Table - then first query - then second query - then report.

The bottom of the report has sum fields that are summing from the
query.
The list above that needs to show all items except for one.

Is that possible?


I use a table with just the name of the item I want to filter on, like
in
some of my reports I want to filter on AREA. I have a table with the
area
names, like area A, area B, and so forth. I then create a form, put a
combo
box on the form (use the wizard to do that for you) and select the
table you
created as the source of the combo box. Then right click on the combo
and
select events. Use the onclick event and start the code builder (the
little
button out to the right.) Put in this code.
Dim strWhere As String
Dim stDocument As String
stDocument = "06-07 plan review approval letter sent"
strWhere = "1=1 "
If Not IsNull(Me.Combo0) Then
strWhere = strWhere & " And [Area]=""" & _
Me.Combo0 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere
DoCmd.Close acForm, "area form 4", acSaveNo
Change report name to your report name (mine was 06-07 plan review
approval
letter sent.) Change your filter to what you need (is used area.)
Change to
your combo box number (mine was 0.)

:

Greetings,
I need to adjust a report so that it filters records as opposed
to the
query doing the filtering.
I can't do this in the query because elsewhere on the report it uses
all the
data from the query, but the main body needs to list all the records
except
for one, by criteria from one of the fields.
Is this possible?

Thanks beforehand.

Jacques

 

[Klatuu]

I may be wrong. Try moving it to the Print event and be sure you are using
the value of the field bound to your control that you don't want printed.

I assume that I'm doing it wrong. It doesn't work at all.

I right click on the field in question, click build event, code builder.
I put in the below and change the Text2 to the name of the field, and
"Hargis" to the data I want filtered, but nothing happens.

Am I missing something?

You can cause a specific record not to print. Use the control on your
report that is bound to the primary key field or some other unique value
int he record. Here is an example.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Text2 = "Hargis" Then
Cancel = True
End If

End Sub

Also, you will want to set all the controls in the section to Can Grow
and Can Shrink = Yes so it will not leave a blank line.

I have:
Table > query > query > report

In general, the report shows positions and hours worked.

The page footer has:
Totals on one line
Totals for only one of the detail records in the second line
Total of the one line minus the second line

We need the details section to show every position but one, while the
page footer info doesn't change.
The one record we need to NOT show in the details section, is still
needed to help produce the page footer.

Does that make it a bit more clearer?





No, it is not possible. A record is either in the report's recordset
or it is not.

Rather than ask the how, tell us what you want to do and maybe we can
offer a suggestion as to how.

I'll have to try that, but won't that filter the whole report.
Remember that I said that the rest of the report needs all the data
from the query.

I can't use the table, as the query is needed to group the data.
Its Table - then first query - then second query - then report.

The bottom of the report has sum fields that are summing from the
query.
The list above that needs to show all items except for one.

Is that possible?


I use a table with just the name of the item I want to filter on, like
in
some of my reports I want to filter on AREA. I have a table with the
area
names, like area A, area B, and so forth. I then create a form, put a
combo
box on the form (use the wizard to do that for you) and select the
table you
created as the source of the combo box. Then right click on the combo
and
select events. Use the onclick event and start the code builder (the
little
button out to the right.) Put in this code.
Dim strWhere As String
Dim stDocument As String
stDocument = "06-07 plan review approval letter sent"
strWhere = "1=1 "
If Not IsNull(Me.Combo0) Then
strWhere = strWhere & " And [Area]=""" & _
Me.Combo0 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere
DoCmd.Close acForm, "area form 4", acSaveNo
Change report name to your report name (mine was 06-07 plan review
approval
letter sent.) Change your filter to what you need (is used area.)
Change to
your combo box number (mine was 0.)

:

Greetings,
I need to adjust a report so that it filters records as opposed
to the
query doing the filtering.
I can't do this in the query because elsewhere on the report it uses
all the
data from the query, but the main body needs to list all the records
except
for one, by criteria from one of the fields.
Is this possible?

Thanks beforehand.

Jacques

 

[Jacques Latoison]

Oh okay, I'll try that, but which control are you referring to?
I assume you mean the text box of the field in question (the one field that
has no duplication - but is not a primary key in the report).

I may be wrong. Try moving it to the Print event and be sure you are using
the value of the field bound to your control that you don't want printed.

I assume that I'm doing it wrong. It doesn't work at all.

I right click on the field in question, click build event, code builder.
I put in the below and change the Text2 to the name of the field, and
"Hargis" to the data I want filtered, but nothing happens.

Am I missing something?

You can cause a specific record not to print. Use the control on your
report that is bound to the primary key field or some other unique
value int he record. Here is an example.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Text2 = "Hargis" Then
Cancel = True
End If

End Sub

Also, you will want to set all the controls in the section to Can Grow
and Can Shrink = Yes so it will not leave a blank line.

I have:
Table > query > query > report

In general, the report shows positions and hours worked.

The page footer has:
Totals on one line
Totals for only one of the detail records in the second line
Total of the one line minus the second line

We need the details section to show every position but one, while the
page footer info doesn't change.
The one record we need to NOT show in the details section, is still
needed to help produce the page footer.

Does that make it a bit more clearer?





No, it is not possible. A record is either in the report's recordset
or it is not.

Rather than ask the how, tell us what you want to do and maybe we can
offer a suggestion as to how.

I'll have to try that, but won't that filter the whole report.
Remember that I said that the rest of the report needs all the data
from the query.

I can't use the table, as the query is needed to group the data.
Its Table - then first query - then second query - then report.

The bottom of the report has sum fields that are summing from the
query.
The list above that needs to show all items except for one.

Is that possible?


I use a table with just the name of the item I want to filter on,
like in
some of my reports I want to filter on AREA. I have a table with the
area
names, like area A, area B, and so forth. I then create a form, put
a combo
box on the form (use the wizard to do that for you) and select the
table you
created as the source of the combo box. Then right click on the
combo and
select events. Use the onclick event and start the code builder (the
little
button out to the right.) Put in this code.
Dim strWhere As String
Dim stDocument As String
stDocument = "06-07 plan review approval letter sent"
strWhere = "1=1 "
If Not IsNull(Me.Combo0) Then
strWhere = strWhere & " And [Area]=""" & _
Me.Combo0 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere
DoCmd.Close acForm, "area form 4", acSaveNo
Change report name to your report name (mine was 06-07 plan review
approval
letter sent.) Change your filter to what you need (is used area.)
Change to
your combo box number (mine was 0.)

:

Greetings,
I need to adjust a report so that it filters records as opposed
to the
query doing the filtering.
I can't do this in the query because elsewhere on the report it
uses all the
data from the query, but the main body needs to list all the
records except
for one, by criteria from one of the fields.
Is this possible?

Thanks beforehand.

Jacques

 

[Klatuu]

I believe that is correct.

Oh okay, I'll try that, but which control are you referring to?
I assume you mean the text box of the field in question (the one field
that has no duplication - but is not a primary key in the report).

I may be wrong. Try moving it to the Print event and be sure you are
using the value of the field bound to your control that you don't want
printed.

I assume that I'm doing it wrong. It doesn't work at all.

I right click on the field in question, click build event, code builder.
I put in the below and change the Text2 to the name of the field, and
"Hargis" to the data I want filtered, but nothing happens.

Am I missing something?




You can cause a specific record not to print. Use the control on your
report that is bound to the primary key field or some other unique
value int he record. Here is an example.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Text2 = "Hargis" Then
Cancel = True
End If

End Sub

Also, you will want to set all the controls in the section to Can Grow
and Can Shrink = Yes so it will not leave a blank line.

I have:
Table > query > query > report

In general, the report shows positions and hours worked.

The page footer has:
Totals on one line
Totals for only one of the detail records in the second line
Total of the one line minus the second line

We need the details section to show every position but one, while the
page footer info doesn't change.
The one record we need to NOT show in the details section, is still
needed to help produce the page footer.

Does that make it a bit more clearer?





No, it is not possible. A record is either in the report's recordset
or it is not.

Rather than ask the how, tell us what you want to do and maybe we can
offer a suggestion as to how.

I'll have to try that, but won't that filter the whole report.
Remember that I said that the rest of the report needs all the data
from the query.

I can't use the table, as the query is needed to group the data.
Its Table - then first query - then second query - then report.

The bottom of the report has sum fields that are summing from the
query.
The list above that needs to show all items except for one.

Is that possible?


I use a table with just the name of the item I want to filter on,
like in
some of my reports I want to filter on AREA. I have a table with
the area
names, like area A, area B, and so forth. I then create a form, put
a combo
box on the form (use the wizard to do that for you) and select the
table you
created as the source of the combo box. Then right click on the
combo and
select events. Use the onclick event and start the code builder
(the little
button out to the right.) Put in this code.
Dim strWhere As String
Dim stDocument As String
stDocument = "06-07 plan review approval letter sent"
strWhere = "1=1 "
If Not IsNull(Me.Combo0) Then
strWhere = strWhere & " And [Area]=""" & _
Me.Combo0 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere
DoCmd.Close acForm, "area form 4", acSaveNo
Change report name to your report name (mine was 06-07 plan review
approval
letter sent.) Change your filter to what you need (is used area.)
Change to
your combo box number (mine was 0.)

:

Greetings,
I need to adjust a report so that it filters records as
opposed to the
query doing the filtering.
I can't do this in the query because elsewhere on the report it
uses all the
data from the query, but the main body needs to list all the
records except
for one, by criteria from one of the fields.
Is this possible?

Thanks beforehand.

Jacques