Filter busted! 1

[teqtaq]

I have 4 Parameters for the Report.
It can be run by
1st and 2nd with 4th (cmdAandB)
or
1st and 3rd with 4th (cmdAandB).

My code does not filter.
It worked with all 4 parameters selected.
Now when I am excluding one - something is busted.


Please, take a look.

Private Sub cmdRunReport_Click()
Dim strWhere As String
On Error GoTo ErrHandler

DoCmd.SetWarnings True


If Forms!Reports!cmbAB = "AandB" And Not IsNull(Forms!Reports!cmbR) Then
strWhere = "Name =" & Chr(34) & Forms!Reports!cmbN.Column(0) & Chr(34) & _
" And Region =" & Chr(34) & Forms!Offers_Hires_Reports!cmbR.Column(0) & Chr(34) '& _

DoCmd.OpenReport ReportName:="ReportAandB", View:=acViewPreview, WhereCondition:=strWhere

End If

If Forms!Reports!cmbAB = "AandB" And Not IsNull(Forms!Reports!cmbN) Then
strWhere = "Name =" & Chr(34) & Forms!Reports!cmbN.Column(0) & Chr(34) & _
" And Ofice =" & Chr(34) & Forms!Offers_Hires_Reports!cmbO.Column(0) & Chr(34) '& _

DoCmd.OpenReport ReportName:="ReportAandB", View:=acViewPreview, WhereCondition:=strWhere

End If

Exit Sub

ErrHandler:
If Err = 2501 Then
' Report canceled - ignore this
Else
MsgBox Err.DESCRIPTION, vbExclamation
End If
End Sub

 

[dhookom]

What have you tried in order to troubleshoot? Have you set a breakpoint and stepped through the code to see what is happening?

Also, "Name" is a poor name for any object that has a name property. Since nearly all objects in Access have a name, don't use name as the name.

Duane
Hook'D on Access
MS Access MVP

 

[teqtaq]

I don't use 'Name' - sorry, bad example.
I stepped trough and I've gotten an info that my third last parameter doesn't get read. I am about to investigate now. Thanks

 

[teqtaq]

All right, I had fixed this by creating 2 separate forms with 3 and 3 parameters each and it is working.

However I am looking for something else.
How do I keep 1 parameter blank and it will mean 'take all values in this field'?


I use to place code into the queries and I used this approach
Forms!Reports!cmbN or Forms!Reports!cmbN is NUll

But now when I am not using quesry parameters I can't fugure it out.
Thanks

 

[dhookom]

I keep my report filtering simple. Check my reply in this thread

http://www.tek-tips.com/viewthread.cfm?qid=1476741.

Duane
Hook'D on Access
MS Access MVP

 

[teqtaq]

dhookom,
I do exactly the same thing (however I would not call it simple because of how many filters I have) but where you are using Me.cboEmpID - I am using Forms!Reports!cmbN.
With data from People Soft Me. does get cranky a lot.

It is also unknown to me why Null gets ignored sometimes.

Do you know why Me. is not always works? I am sure your code would save me a lot of space.

Thanks

 

[dhookom]

I don't understand what you are asking or what PeopleSoft has to do with anything. "Me" is only used in code within a Form or Report.

Duane
Hook'D on Access
MS Access MVP

 

[teqtaq]

I AM I AM using it within Report or Form.
I thought PS data can be the cause of the Me. failure.

I have to try again. Thanks