Report Filtering Issue

G

Guest

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.
 
G

Guest

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?
 
R

Rick Brandt

chickalina said:
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?
Click to expand...

Okay then lets start at the beginning. You might not even need to replace the
SQL in the QueryDef. That is one way to do it, but you might be able to just
supply a WHERE argument to the OpenReport method which will filter the report as
it opens. (the code you copied isn't very good anyway).

The premise of the code is okay which is to examine a bunch of controls on the
form and any use any that have entries as filter criteria for the report. That
is what you want to accomplish right? However; instead of using Like "*" for
controls that have no entry they should just be left out of the WHERE clause we
are building altogether. That will provide a "cleaner" and more efficient
filter.

So we do need a variable to hold the WHERE clause we are building, but I think
we can get by with a single variable rather than a whole bunch of them. We will
just selectively decide to add more "AND..." pieces to it or not as we go.

To start off lets build a basic structure that will open the report
unfiltered...

Dim strWhere as String
strWhere = "1 = 1 "
DoCmd.OpenReport "rpt_ideasbycountry", acPreview,,strWhere

Try that much. The filter "1 = 1" is always true and that is why we get all
records from the report. The reason I start with a filter that does nothing is
that for all other filters that we will add onto that base we can always use
"AND" in front of them since we already have a filter string to start us off.
Otherwise we would always have to test if a filter being added is the first one
we are using (no "AND") or if it is not the first (include "AND"). This way we
just always include "AND" and don't worry about it.

Now lets add one of the optional filters.

If Not IsNull(Me.cboCountry) Then
strWhere = strWhere & "AND strCountry = '" & Me.cboCountry & "' "
End If

Notice I left a space at the end of "1 = 1 " and also at the end of the string
above. That is so the word "AND" is not crammed up against the previous
filter's last character.

So...adding that to what we had previously...

Dim strWhere as String
strWhere = "1 = 1 "

If Not IsNull(Me.cboCountry) Then
strWhere = strWhere & "AND strCountry = '" & Me.cboCountry & "' "
End If

DoCmd.OpenReport "rpt_ideasbycountry", acPreview,,strWhere

If you try that code you should find that you get all records when you have no
value entered in cboCountry and it should be filtered by any entry you do make
in cboCountry.

All you have to do if that works for you is add additional If-Then blocks for
all of the other controls that will be used to add filter criteria. As long as
they are all Text values the If-Then block would be identical to the one above
except for the name of the control. For Numeric values just eliminate the
single quotes from the If-Then block and for Date values replace the single
quotes with the # symbol.

Post back if you get stuck.
 
G

Guest

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?
 
G

Guest

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
Report Options 4
Crosstab Query Building Report 2
Report based on form filter? 1
Need help in printing vba code 0
Including blank fields in results when filtering a report 1
Combine Code 2

Top