sql statements 2

[striker73]

I want to create a report but allow users to filter the information in the report. It is my understanding that I can create my own SQL statement and then some how apply this as a filter to my query. The code I was looking at goes something like this:

'Create the sql statement. 'Description' is one of the fields I have in my query and cboIndustry is a combo box on my form.

SQL_statement = "Select * from MyQuery Where Industry.Description = " & cboIndustry.Text

DoCmd.OpenReport MyReport, acPrevies, SQL_Statement


This doesn't seem to be working. Any ideas? Thanks!

 

[sko]

Assuming you have a "Preview Report" button on the same form with the combo box, add this to the button's click event:

Private Sub cmdReportPreview_Click()
On Error GoTo Err_cmdReportPreview_Click

Dim stDocName As String
Dim stSQL As String
stSQL = "[Description] = " & Chr(34) [cboName] & Chr(34)

stDocName = "ReportName"
DoCmd.OpenReport stDocName, acPreview, , stSQL

Exit_cmdReportPreview_Click:
Exit Sub

Err_cmdReportPreview_Click:
MsgBox Err.Description
Resume Exit_cmdReportPreview_Click

End Sub


Another way to do this is to modify the query the report is based on. In the query grid's criteria line for the description column add:

[forms]![NameOfForm]![NameOfComboBox]

This tells the query to get it's criteria from your combo box. Then modify the "Preview Report" button like so:


Private Sub cmdReportPreview_Click()
On Error GoTo Err_cmdReportPreview_Click

Dim stDocName As String

stDocName = "ReportName"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdReportPreview_Click:
Exit Sub

Err_cmdReportPreview_Click:
MsgBox Err.Description
Resume Exit_cmdReportPreview_Click

End Sub

HTH

 

[striker73]

Thanks so much!!! It works perfectly when I use the [forms]![formname]![combobox] command in the criteria field of the query, but I think I want to have the criteria in the code because I have different options for the user on how they can display the information (by industry, by zip code...)

I'm getting a "Data type mismatch in criteria expression" error when I use the SQL string. When I output what my string is, it says [Industry_ID] = "4" which looks right to me. I have tried single quotes instead of double, but this doesn't seem to work. Any ideas? Thanks!!!

 

[JoeMiller]

No quotes, if the field is a number, you're turning it into a string by putting the quotes around it. Hence, data mismatch.

HTH Joe Miller
joe.miller@flotech.net

 

[striker73]

thanks so much!!!!!!!!!!!!!!!!!