Report Filtering Issue

C

chickalina

I'm having a problem filtering in my report... The code is pasted below. I
thought it would work in 2003 since it works in 2000, but not so. I'd like to
filter the report to show the records for the criteria selected.

Private Sub cmd_PreviewPlanningReport_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strCountry As String
Dim strStructural As String
Dim strCategory As String
Dim strJurisdiction As String
Dim strBenefitType As String
Dim strIdeaCategory As String
Dim strSBU As String
Dim strAuthority As String
Dim strAudittype As String
Dim strCurrentStatus As String
Dim strHWContact As String
Dim strExternalContact As String
Dim strSQL As String
Dim stDocName As String
Set db = CurrentDb

If IsNull(Me.cboCountry.Value) Then
strCountry = " Like '*' "
Else
strCountry = "='" & Me.cboCountry.Value & "' "
End If

If IsNull(Me.cboStructural.Value) Then
strStructural = " Like '*' "
Else
strStructural = "='" & Me.cboStructural.Value & "' "
End If

If IsNull(Me.cboCurrentStatus.Value) Then
strCurrentStatus = " Like '*' "
Else
strCurrentStatus = "='" & Me.cboCurrentStatus.Value & "' "
End If

If IsNull(Me.cboJurisdiction.Value) Then
strJurisdiction = " Like '*' "
Else
strJurisdiction = "='" & Me.cboJurisdiction.Value & "' "
End If

If IsNull(Me.cboBenefitType.Value) Then
strBenefitType = " Like '*' "
Else
strBenefitType = "='" & Me.cboBenefitType.Value & "' "
End If

If IsNull(Me.cboHWContact.Value) Then
strHWContact = " Like '*' "
Else
strHWContact = "='" & Me.cboHWContact.Value & "' "
End If

If IsNull(Me.cboExternalContact.Value) Then
strExternalContact = " Like '*' "
Else
strExternalContact = "='" & Me.cboExternalContact.Value & "' "
End If

If IsNull(Me.cboCurrentStatus.Value) Then
strCurrentStatus = " Like '*' "
Else
strCurrentStatus = "='" & Me.cboCurrentStatus.Value & "' "
End If


strSQL = "SELECT tbl_issues_log.* " & _
"FROM tbl_issues_log " & _
("WHERE " + varWhere) & _
" ORDER BY tbl_issues_log.issuedescription;"

DoCmd.OpenReport "rpt_ideasbycountry", acPreview
' DoCmd.Close acForm, Me.Name

Set qdr = Nothing
Set db = Nothing

' Debug.Print SQL
End Sub
 
R

Rick Brandt

chickalina said:
I'm having a problem filtering in my report... The code is pasted
below. I thought it would work in 2003 since it works in 2000, but
not so. I'd like to filter the report to show the records for the
criteria selected.

Private Sub cmd_PreviewPlanningReport_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strCountry As String
Dim strStructural As String
Dim strCategory As String
Dim strJurisdiction As String
Dim strBenefitType As String
Dim strIdeaCategory As String
Dim strSBU As String
Dim strAuthority As String
Dim strAudittype As String
Dim strCurrentStatus As String
Dim strHWContact As String
Dim strExternalContact As String
Dim strSQL As String
Dim stDocName As String
Set db = CurrentDb

If IsNull(Me.cboCountry.Value) Then
strCountry = " Like '*' "
Else
strCountry = "='" & Me.cboCountry.Value & "' "
End If

If IsNull(Me.cboStructural.Value) Then
strStructural = " Like '*' "
Else
strStructural = "='" & Me.cboStructural.Value & "' "
End If

If IsNull(Me.cboCurrentStatus.Value) Then
strCurrentStatus = " Like '*' "
Else
strCurrentStatus = "='" & Me.cboCurrentStatus.Value & "' "
End If

If IsNull(Me.cboJurisdiction.Value) Then
strJurisdiction = " Like '*' "
Else
strJurisdiction = "='" & Me.cboJurisdiction.Value & "' "
End If

If IsNull(Me.cboBenefitType.Value) Then
strBenefitType = " Like '*' "
Else
strBenefitType = "='" & Me.cboBenefitType.Value & "' "
End If

If IsNull(Me.cboHWContact.Value) Then
strHWContact = " Like '*' "
Else
strHWContact = "='" & Me.cboHWContact.Value & "' "
End If

If IsNull(Me.cboExternalContact.Value) Then
strExternalContact = " Like '*' "
Else
strExternalContact = "='" & Me.cboExternalContact.Value & "' "
End If

If IsNull(Me.cboCurrentStatus.Value) Then
strCurrentStatus = " Like '*' "
Else
strCurrentStatus = "='" & Me.cboCurrentStatus.Value & "' "
End If


strSQL = "SELECT tbl_issues_log.* " & _
"FROM tbl_issues_log " & _
("WHERE " + varWhere) & _
" ORDER BY tbl_issues_log.issuedescription;"

DoCmd.OpenReport "rpt_ideasbycountry", acPreview
' DoCmd.Close acForm, Me.Name

Set qdr = Nothing
Set db = Nothing

' Debug.Print SQL
End Sub
Click to expand...

That code would not work in Access 2000 either. You go to the trouble of
setting the value of a lot of variables but then you never do anything with
them. At the end you just open the report with no filter. You Dim a querydef
object, but you never use it anywhere.

I suspect you copied and pasted this code but lost a few lines of code along the
way.
 
R

Rick Brandt

chickalina said:
I see what you mean.... what would you suggest I put at the
bottom?...
Click to expand...

Do you have some similar code in an Access 200 app that works? If you look at
that you can see what you need.

If I had to guess I would say that the original code used the string variables
to set a master variable called varWhere (which your code uses, but never Dims
or sets) and then that variable is used to compose new SQL that is to be
inserted into the QueryDef that is used by the report. That SQL is in strSQL in
your code, but since varWhere is not being set to anything strSQL is currently
holding a SQL statement with no WHERE clause at all.

Where exactly did you get this code? Clearly it is not your own creation or you
would see these rather blatant problems. If you still have the source somewhere
you should be able to compare that to what you have.
 
C

chickalina

Rick, to be honest with you, I have no clue as to what I'm doing... I just
copied some code posted in the newsgroups and I thought to apply it to what
I'm doing... Obviously I'm doing something wrong.... can you help?
 
C

chickalina

Rick,
Thanks for the explanation, it really explains why you are writing code that
way... when I click on the button now the End/Debug window comes up and this
line is yellow:

DoCmd.OpenReport "rpt_ideasbycountry", acPreview, , strWhere

I don't understand because it's a simple Preview command... should I be
using acViewPreview?
 
C

chickalina

Sorry, I meant that when I pick United States... when there is nothing chosen
(drop down), then all the records appear.
 
R

Rick Brandt

chickalina said:
Rick,
Thanks for the explanation, it really explains why you are writing
code that way... when I click on the button now the End/Debug window
comes up and this line is yellow:

DoCmd.OpenReport "rpt_ideasbycountry", acPreview, , strWhere

I don't understand because it's a simple Preview command... should I
be using acViewPreview?
Click to expand...

Yes I copied and pasted that without seeing that the constant was incorrect.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search forms results not opening report in Preview 3
Search Form stops working on Exit 5
VBA Export to PDF 0
Match style 0
Working outlook via vba code with xlsx - error 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Making a macro only work on certain files 2

Top