Hi,

I have created a report and a form filter, when I select an option from the combo box on the form filter I get the run=time error '13' Type mismatch

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""

This is the complete code

Option Compare Database

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, "Rpt_Trade" 'Close the Trades report.
DoCmd.Restore 'Restore the window size

End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "Rpt_Trades", A_PREVIEW 'Open Trades Report.
DoCmd.Maximize 'Maximize the report window.

End Sub

Private Sub Set_Filter_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![Rpt_Trades].Filter = strSQL
Reports![Rpt_Trades].FilterOn = True
End If

End Sub


Would appreciate any help where I am going wrong

Have you looked at a

debug.print strSQL

in the immediate window.

Or insert msgbox (strSQL) before the opening of the report.

Is any of the filters numeric?

Yes the Log No is an autonum

If its numeric you don't need the quotes in the expression.

Sorry very new to this which part of the expression would I take the quotes out

Sorry very new to this which part of the expression would I take the quotes out

You have:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""

If I put this on 1 line and clean up a bit, you'd get:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] = '" & Me("Filter" & intCounter) & "' And "

Notice I replaced the chr(34) with a single quote (which is positioned within a part enclosed with double quotes; confusing isn't it).
I also deleted 1 double quote at the end.
The above syntax will work if you're comparing strings.

If you need to compare numeric values, you need to get rid off the single quotes, so you'd get:
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] = " & Me("Filter" & intCounter) & " And "

Instead of using a for...next loop, you might want to consider (in this case) to get rid of the loop and write the code out explicitly (Filter1, filter2, ...)

put in a msgbox (strSQL) after completing the construction of it, so you can check if it's ok.

Great that worked perfectly, how would I have to change the code if the user wanted to filter on a date?

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] = #" & Me("Filter" & intCounter) & "# And "

Ok, in order to do this:
- you open the filter-form the same way you are already opening it (through a button on another form, double-clicking the form in the formslist, ...)

- you delete the following line from the Form_Open event:
DoCmd.OpenReport "Rpt_Trades", A_PREVIEW 'Open Trades Report
this will prevent that the report opens at the same time as the form

- you put a commandbutton on the form, and in the on click event for this button you put:

...
code that creates the strSQL-string based on the filtering combos
...

DoCmd.OpenReport "Rpt_Trades", acViewPreview, , strSQL

Ok I added it to my search button which runs the code below but then I get a Run-time error '2451' The report name 'Rpt_Trades' you entered is misspelled or refers to a report that isn't open or doesn't exist

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

' Build SQL String.
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] = '" & Me("Filter" & intCounter) & "' And "

End If
Next

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

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

DoCmd.OpenReport "Rpt_Trades", acViewPreview, , strSQL


End Sub

Try changing it to:

Thats perfect thank you very much

Now one more question is it possible to put a command button onto the Report Preview so that the user has the option to print or return to the main menu?

This is possible in Access 2010, don't know if it's possible in previous versions.

In the "print-button":

DoCmd.PrintOut

In the "go back button":

DoCmd.Close acReport, Me.Name

Ok, I am running 2002 so don't have the option to add the buttons, so how could I still use the filter form and then link that to a subform that will show the records and allow me to add the additional buttons, what would I need to change in the sql statement.

Many Thanks

- create a continuous form (let's name it frmTradePreview) which is based on the same data as your report (include same fields)

- on this form add a hidden textbox (name it txtSQL)

- in the filterform change the code for the command button into (this will open frmTradePreview with the filter like the report):


- in the frmTradePreview add 2 commandbuttons:
one to print the report (name it cmdPrint):


the other button you can use to close frmTradePreview and go back to main menu, filter form ot whatever you want to.