Using a filter to show results on a report

[sooz9]

Hello.

I'm trying to use a filter (a form I created) to filter the results on my
report (RptBLDowntimeFilter). I typed in the code that was on an example and
am having problems getting the report to filter based on my selections. I
didn't change any of the code from the example because I didn't understand
it. I know there is a problem with my code.

When I set the first filter to "capper" from a drop down list and click the
"Filter" cbo button. I get the following error message: Syntax errpr in
querry expression '([PrimaryReason]="10"_)'

The '10' refers to the auto number (Primary key) associated with capper from
my tblBLPrimaryReasonList table.
tblBLPrimary ReasonList: ID= auto number BLPrimaryReason=text

My form frmFilter has 4 drop down lists to choose from (Filter1, Filter2,
Filter3, Filter4) The Row source for Filter1 (ie.Primary Reason unbound) is
SELECT [Tbl BL Primary Reason List].ID, [Tbl BL Primary Reason
List].BLPrimaryReason FROM [Tbl BL Primary Reason List] ORDER BY
[BLPrimaryReason]; ----WITH Bound column set to 1

If I set the bound column to 2 I get the same error message except it says
the word "Capper" instead of the number 10.

How do I get the report to filter by the item I select in my drop down list?

Here is all the code associated with my frmFilter:
Private Sub Clear_Click()
Dim intCounter As Integer
For intCounter = 1 To 4
Me("Filter" & intCounter) = ""
Next
End Sub


Private Sub Close_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "RptBLDowntimeFilter" 'Close the BL Downtime
Filter Report.
DoCmd.Restore 'Restore the window size
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "RptBLDowntimeFilter", A_PREVIEW 'Open BL Downtime
Filter Report.
DoCmd.Maximize 'Maximize the report window.
End Sub

Private Sub SetFilter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " _
And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))

' Set the Filter property.
Reports![rptBLDowntimeFilter].Filter = strSQL
Reports![rptBLDowntimeFilter].FilterOn = True
End If
End Sub

Any assistance would be greatly appreciated.

 

[דרור]

Can you add a MsgBox to display the SQL query?
For example:


If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))
MSGNOX "s=" & strSQL
' Set the Filter property.
Reports![rptBLDowntimeFilter].Filter = strSQL
Reports![rptBLDowntimeFilter].FilterOn = True

Once you have the query can you please post it here?

Dror

 

[sooz9]

I'm not sure what you are asking for. Am I making a new querry? I'm not that
familuar using codes.

 

[דרור]

Look at your own code:
Reports![rptBLDowntimeFilter].Filter = strSQL

In this line you set the report's filter.

What is the value of the strSQL?

Simply add
MsgBox strSQL
one line before it so you can see the query. I'll bet that one look will
give you the answer why this query fails.

 

[sooz9]

The message box says : s= [PrimaryReason] = "10"_

I'm not sure what you are asking for. Am I making a new querry? I'm not that
familuar using codes.

Can you add a MsgBox to display the SQL query?
For example:


If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))
MSGNOX "s=" & strSQL
' Set the Filter property.
Reports![rptBLDowntimeFilter].Filter = strSQL
Reports![rptBLDowntimeFilter].FilterOn = True

Once you have the query can you please post it here?

Dror

 

[sooz9]

I Have a table called tblBLPrimaryReasonList. This table has the following
fields: ID (autonumber) and BLPrimaryReason (text). In this table I have
list lets say 50 diferent downtime reasons.

The FrmFilter also a user to select using a cbo box any one of these reasons
to filter the report by. So for example, when I select "Capper" to filter my
report by, the current error message says it it looking for me to have
selected "10"....the autonumber assigned to capper.

How is the code written so that it knows to show my filtered data based on
the text and not keep looking for the data in number format?