filter report based on form filters

I have a mainform called PendingsMain and a subform called
PendingsSub. You can filter the subform by different filters and this
works fine.

i want to create a report that will print out the results of the users
filters or print everything if there is no filters submitted within
the form.

Any suggestions

This is what i have so far:

Dim StrWhere As String
With Me.[FPastDuePendingsSub].Form
If Me.FPastDuePendingsSub.Form.FilterOn = True Then
StrWhere = Me.FPastDuePendingsSub.Form.Filter & _
" AND ([opid] = """ & Me.cboopid & """)"
End If
End With
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere


if an "opid" is not selected but another filter is like "ProdCD"
"DateIn", "Policy" it will not filter the report. I need to take in
account all of the filters that could be selected or left null just
not sure how.

lilbit27 wrote:

I have a mainform called PendingsMain and a subform called
PendingsSub. You can filter the subform by different filters and this
works fine.

i want to create a report that will print out the results of the users
filters or print everything if there is no filters submitted within
the form.

This is what i have so far:

Dim StrWhere As String
With Me.[FPastDuePendingsSub].Form
If Me.FPastDuePendingsSub.Form.FilterOn = True Then
StrWhere = Me.FPastDuePendingsSub.Form.Filter & _
" AND ([opid] = """ & Me.cboopid & """)"
End If
End With
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere


if an "opid" is not selected but another filter is like "ProdCD"
"DateIn", "Policy" it will not filter the report. I need to take in
account all of the filters that could be selected or left null just
not sure how.


The general idea is along these lines:

With Me.[FPastDuePendingsSub].Form
If .FilterOn = True Then
strWhere = " And " & .Filter
End If

If Not IsNull(Me.cboopid) Then
strWhere = strWhere & " And opid=""" & Me.cboopid & """"
End If
If Not IsNull(Me.cboProdCD) Then
strWhere = strWhere & " And ProdCD=""" & Me.cboProdCD &
""""
End If
. . .

End With

DoCmd.OpenReport "RPendDetail", acViewPreview, , _
Mid(strWhere, 6)

--
Marsh
MVP [MS Access]

On May 24, 12:45 pm, Marshall Barton wrote:
lilbit27 wrote:
I have a mainform called PendingsMain and a subform called
PendingsSub. You can filter the subform by different filters and this
works fine.

i want to create a report that will print out the results of the users
filters or print everything if there is no filters submitted within
the form.

This is what i have so far:

Dim StrWhere As String
With Me.[FPastDuePendingsSub].Form
If Me.FPastDuePendingsSub.Form.FilterOn = True Then
StrWhere = Me.FPastDuePendingsSub.Form.Filter & _
" AND ([opid] = """ & Me.cboopid & """)"
End If
End With
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

if an "opid" is not selected but another filter is like "ProdCD"
"DateIn", "Policy" it will not filter the report. I need to take in
account all of the filters that could be selected or left null just
not sure how.

The general idea is along these lines:

With Me.[FPastDuePendingsSub].Form
If .FilterOn = True Then
strWhere = " And " & .Filter
End If

If Not IsNull(Me.cboopid) Then
strWhere = strWhere & " And opid=""" & Me.cboopid & """"
End If
If Not IsNull(Me.cboProdCD) Then
strWhere = strWhere & " And ProdCD=""" & Me.cboProdCD &
""""
End If
. . .

End With

DoCmd.OpenReport "RPendDetail", acViewPreview, , _
Mid(strWhere, 6)

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

I get a syntax missing operator in query expression if I pick an opid,
or pick a prodcd or leave it blank

lilbit27 wrote:

On May 24, 12:45 pm, Marshall Barton wrote:
lilbit27 wrote:
I have a mainform called PendingsMain and a subform called
PendingsSub. You can filter the subform by different filters and this
works fine.

i want to create a report that will print out the results of the users
filters or print everything if there is no filters submitted within
the form.

This is what i have so far:

Dim StrWhere As String
With Me.[FPastDuePendingsSub].Form
If Me.FPastDuePendingsSub.Form.FilterOn = True Then
StrWhere = Me.FPastDuePendingsSub.Form.Filter & _
" AND ([opid] = """ & Me.cboopid & """)"
End If
End With
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

if an "opid" is not selected but another filter is like "ProdCD"
"DateIn", "Policy" it will not filter the report. I need to take in
account all of the filters that could be selected or left null just
not sure how.

The general idea is along these lines:

With Me.[FPastDuePendingsSub].Form
If .FilterOn = True Then
strWhere = " And " & .Filter
End If

If Not IsNull(Me.cboopid) Then
strWhere = strWhere & " And opid=""" & Me.cboopid & """"
End If
If Not IsNull(Me.cboProdCD) Then
strWhere = strWhere & " And ProdCD=""" & Me.cboProdCD &
""""
End If
. . .

End With

DoCmd.OpenReport "RPendDetail", acViewPreview, , _
Mid(strWhere, 6)


I get a syntax missing operator in query expression if I pick an opid,
or pick a prodcd or leave it blank


First, add
MsgBox Mid(strWhere, 6)
after the End With and see if that helps you spot where the
code has a mistake (probably a missing space).

If you can't find the problem. post a Copy/Paste of your
code.

--
Marsh
MVP [MS Access]

On May 24, 8:40 pm, Marshall Barton wrote:
lilbit27 wrote:
On May 24, 12:45 pm, Marshall Barton wrote:
lilbit27 wrote:
I have a mainform called PendingsMain and a subform called
PendingsSub. You can filter the subform by different filters and this
works fine.

i want to create a report that will print out the results of the users
filters or print everything if there is no filters submitted within
the form.

This is what i have so far:

Dim StrWhere As String
With Me.[FPastDuePendingsSub].Form
If Me.FPastDuePendingsSub.Form.FilterOn = True Then
StrWhere = Me.FPastDuePendingsSub.Form.Filter & _
" AND ([opid] = """ & Me.cboopid & """)"
End If
End With
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

if an "opid" is not selected but another filter is like "ProdCD"
"DateIn", "Policy" it will not filter the report. I need to take in
account all of the filters that could be selected or left null just
not sure how.

The general idea is along these lines:

With Me.[FPastDuePendingsSub].Form
If .FilterOn = True Then
strWhere = " And " & .Filter
End If

If Not IsNull(Me.cboopid) Then
strWhere = strWhere & " And opid=""" & Me.cboopid & """"
End If
If Not IsNull(Me.cboProdCD) Then
strWhere = strWhere & " And ProdCD=""" & Me.cboProdCD &
""""
End If
. . .

End With

DoCmd.OpenReport "RPendDetail", acViewPreview, , _
Mid(strWhere, 6)

I get a syntax missing operator in query expression if I pick an opid,
or pick a prodcd or leave it blank

First, add
MsgBox Mid(strWhere, 6)
after the End With and see if that helps you spot where the
code has a mistake (probably a missing space).

If you can't find the problem. post a Copy/Paste of your
code.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

I still get the error messagim StrWhere As String
With Me.[FPastDuePendingsSub].Form
If .FilterOn = True Then
StrWhere = " And " & .Filter
End If

If Not IsNull(Me.CboOpid) Then
StrWhere = StrWhere & " And opid=""" & Me.CboOpid &
""""
End If
If Not IsNull(Me.cbofilterProd) Then
StrWhere = StrWhere & " And ProdCD=""" &
Me.cbofilterProd & """"
End If

End With
MsgBox Mid(StrWhere, 6)
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWheree

lilbit27 wrote:
On May 24, 8:40 pm, Marshall Barton wrote:
lilbit27 wrote:
On May 24, 12:45 pm, Marshall Barton wrote:

The general idea is along these lines:
[snip part that looks correct]
DoCmd.OpenReport "RPendDetail", acViewPreview, , _
Mid(strWhere, 6)

I get a syntax missing operator in query expression if I pick an opid,
or pick a prodcd or leave it blank
[snip part that looks correct]
MsgBox Mid(StrWhere, 6)
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWheree


You messed up the strwhere part of the OpenReport line:

DoCmd.OpenReport "RPendDetail", acViewPreview, , _
Mid(strWhere, 6)

--
Marsh
MVP [MS Access]

On May 25, 8:29 pm, Marshall Barton wrote:
lilbit27 wrote:
On May 24, 8:40 pm, Marshall Barton wrote:
lilbit27 wrote:
On May 24, 12:45 pm, Marshall Barton wrote:

The general idea is along these lines:

[snip part that looks correct] DoCmd.OpenReport "RPendDetail", acViewPreview, , _
Mid(strWhere, 6)

I get a syntax missing operator in query expression if I pick an opid,
or pick a prodcd or leave it blank

[snip part that looks correct]

MsgBox Mid(StrWhere, 6)
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWheree

You messed up the strwhere part of the OpenReport line:

DoCmd.OpenReport "RPendDetail", acViewPreview, , _
Mid(strWhere, 6)

--
Marsh
MVP [MS Access]

It's not recognizing the filters I if I put the mid(strWhere,6) the
report open ups with blank records. i I take that out it gives me
all the records even when I filter by a particular opid or prodcd.

This is what I had orginally :
With Me.[FPastDuePendingsSub].Form
If Me.FPastDuePendingsSub.Form.FilterOn = True Then
StrWhere = Me.FPastDuePendingsSub.Form.Filter & _
' " AND ([opid] = """ & Me.cboopid & """)"
End If
' End With
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

and this works if I filter by an opid. Even if I add 5 more filters
as long as the opid is filtered it will work. the problem is I may not
want to isolate just on opid and just choose one of the other 5
filters.

lilbit27 wrote:
It's not recognizing the filters I if I put the mid(strWhere,6) the
report open ups with blank records. i I take that out it gives me
all the records even when I filter by a particular opid or prodcd.

This is what I had orginally :
With Me.[FPastDuePendingsSub].Form
If Me.FPastDuePendingsSub.Form.FilterOn = True Then
StrWhere = Me.FPastDuePendingsSub.Form.Filter & _
' " AND ([opid] = """ & Me.cboopid & """)"
End If
' End With
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

and this works if I filter by an opid. Even if I add 5 more filters
as long as the opid is filtered it will work. the problem is I may not
want to isolate just on opid and just choose one of the other 5
filters.


If it didn't do what you want, then you need to determine
why, not just discard a critical part of the code.

The code I suggested is a very common way to do what you
want, so I conclude that you have something pecular in your
situation or there is some other mistake in the code. The
reason I wanted you to add the message box is so you can see
the resulting filter string and try to see what's wrong with
it. If you are unable to determine why you are getting the
error message, then change the MsgBox to:
Debug.Print Mid(StrWhere, 6)
After you run the code you can view the Debug window (using
Ctrl+G or the View menu) and Copy/Paste the generated filter
along with the code that was used. Maybe I can figure it
out with that information.

--
Marsh
MVP [MS Access]

On May 27, 11:16 am, Marshall Barton wrote:
lilbit27 wrote:
It's not recognizing the filters I if I put the mid(strWhere,6) the
report open ups with blank records. i I take that out it gives me
all the records even when I filter by a particular opid or prodcd.

This is what I had orginally :
With Me.[FPastDuePendingsSub].Form
If Me.FPastDuePendingsSub.Form.FilterOn = True Then
StrWhere = Me.FPastDuePendingsSub.Form.Filter & _
' " AND ([opid] = """ & Me.cboopid & """)"
End If
' End With
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

and this works if I filter by an opid. Even if I add 5 more filters
as long as the opid is filtered it will work. the problem is I may not
want to isolate just on opid and just choose one of the other 5
filters.

If it didn't do what you want, then you need to determine
why, not just discard a critical part of the code.

The code I suggested is a very common way to do what you
want, so I conclude that you have something pecular in your
situation or there is some other mistake in the code. The
reason I wanted you to add the message box is so you can see
the resulting filter string and try to see what's wrong with
it. If you are unable to determine why you are getting the
error message, then change the MsgBox to:
Debug.Print Mid(StrWhere, 6)
After you run the code you can view the Debug window (using
Ctrl+G or the View menu) and Copy/Paste the generated filter
along with the code that was used. Maybe I can figure it
out with that information.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Dim StrWhere As String
With Me.[FPastDuePendingsSub].Form
If .FilterOn = True Then
If Not IsNull(Me.CboOpid) Then
StrWhere = .Filter & " AND ([opid] = """ & Me.CboOpid & """)"
Else
StrWhere = .Filter
End If
If Not IsNull(Me.cbofilterProd) Then
StrWhere = .Filter & " AND ([prodcd] = """ & Me.cbofilterProd &
""")"
Else
StrWhere = .Filter
End If
If Not IsNull(Me.cboCmtCd) Then
StrWhere = .Filter & " And ([excd] = """ & Me.cboCmtCd &
""")"
Else
StrWhere = .Filter
End If
End If
End With
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

this is what I have everytime I add a piece of code the previous
filter does not work. If no filter is picked the report works and if
I pick a excd it works but now the opid and prodcd does not work. If
I remoe the excd filter the prodcd will work but not the opid and so
on.

On May 24, 8:40 pm, Marshall Barton wrote:
The code I suggested is a very common way to do what you
want, so I conclude that you have something pecular in your
situation or there is some other mistake in the code. The
reason I wanted you to add the message box is so you can see
the resulting filter string and try to see what's wrong with
it. If you are unable to determine why you are getting the
error message, then change the MsgBox to:
Debug.Print Mid(StrWhere, 6)
After you run the code you can view the Debug window (using
Ctrl+G or the View menu) and Copy/Paste the generated filter
along with the code that was used. Maybe I can figure it
out with that information.
-

lilbit27 wrote:
Dim StrWhere As String
With Me.[FPastDuePendingsSub].Form
If .FilterOn = True Then
If Not IsNull(Me.CboOpid) Then
StrWhere = .Filter & " AND ([opid] = """ & Me.CboOpid & """)"
Else
StrWhere = .Filter
End If
If Not IsNull(Me.cbofilterProd) Then
StrWhere = .Filter & " AND ([prodcd] = """ & Me.cbofilterProd &
""")"
Else
StrWhere = .Filter
End If
If Not IsNull(Me.cboCmtCd) Then
StrWhere = .Filter & " And ([excd] = """ & Me.cboCmtCd &
""")"
Else
StrWhere = .Filter
End If
End If
End With
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

this is what I have everytime I add a piece of code the previous
filter does not work. If no filter is picked the report works and if
I pick a excd it works but now the opid and prodcd does not work. If
I remoe the excd filter the prodcd will work but not the opid and so
on.


That's a rather innovative modification of the code I
suggested, but that logic is just not going to work. You
need to go back to the logic I originally posted and find
the problem there without taking off in all these other
directions.

With Me.[FPastDuePendingsSub].Form
If .FilterOn = True Then
strWhere = " And " & .Filter
End If

If Not IsNull(Me.cboopid) Then
strWhere = strWhere & " And opid=""" & Me.cboopid & """"
End If
If Not IsNull(Me.cboProdCD) Then
strWhere = strWhere & " And ProdCD=""" _
& Me.cboProdCD & """"
End If
If Not IsNull(Me.cboCmtCd) Then
strWhere = strWhere & " And excd = """ & _
Me.cboCmtCd & """"
End If
End With
Debug.Print Mid(strWhere, 6)
DoCmd.OpenReport "RPendDetail", acViewPreview, , _
Mid(strWhere, 6)

Be sure to use the Debug - Compile menu item to detect any
syntax errors and get those corrected before running a test.

I expect that to fail in the same way it did before, but
this time post back with the strwhere string from the Debug
window. Also post a Copy/Paste of the code as it was at the
time of your test so I can try to translate the problem in
where clause to the code that generated it.

It may also be important for me to know the data type of the
opid, ProdCD and excd fields in the report's record source
table (the code assumes that all three fields are Text
fields).

--
Marsh
MVP [MS Access]