Error 3075-Run Query after multi select listbox is utilized

0
votes

have a search form in Access 2010 that filters FYs and Quarters based on certain criteria and opens them in a query. One of the criteria is an unbound multi-select list box, SelectTime (Where a person selects "FY15-Q1 and FY15 Q2, for example. The data are stored in a query, z_Basis_QSReport5_Proposal Details. I keep getting an error 3075. Can someone help me with the code? 

Private Sub Command56_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
   Set qdf = db.QueryDefs("z_Basis_QSReport5_Proposal Details_For_Report")
For Each varItem In Me!SelectTime.ItemsSelected
  strCriteria = strCriteria & ",'" & Me!SelectTime.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
  MsgBox "You did not select anything from the list" _
         , vbExclamation, "Nothing to find!"
  Exit Sub
   End If
   strCriteria = Right(strCriteria, Len(strCriteria) - 1)
   strSQL = "SELECT * FROM z_Basis_QSReport5_Proposal Details " & _
            "WHERE z_Basis_QSReport5_Proposal Details.CriteriaFY IN(" &           strCriteria & ");"
   qdf.SQL = strSQL
   DoCmd.OpenQuery "z_Basis_QSReport5_Proposal Details_For_Report"
   Set db = Nothing
   Set qdf = Nothing
End Sub
2
vbams-accesslistbox

2 Answers

0
votes

I agree with @LiamH that you need to surround your query names with square brackets.

Also it looks like you're trying to change the SQL of a query on the fly - and then call the query before you've saved the changes

   qdf.SQL = strSQL
   **qdf.close**
   DoCmd.OpenQuery "z_Basis_QSReport5_Proposal Details_For_Report"

That being said I think you should be looking at parameter queries or just opening the SQL directly.

0
votes

When creating query, table, and field names; it is best practice to avoid spaces. However, there is a solution.

When you use SQL and you have a table name with spaces you need to encapsulate it in square brackets. like so:

"SELECT * FROM [z_Basis_QSReport5_Proposal Details] & _
        "WHERE [z_Basis_QSReport5_Proposal Details].CriteriaFY .....

EDIT

Before, I mentioned that you should maybe put square brackets around the query name, but if you look at the example here you will see that spaces are acceptable in this instance.

If we go back to your query, strcriteria is a string and therefore you need to put single quotes around it:

 strSQL = "SELECT * FROM [z_Basis_QSReport5_Proposal Details] " & _
          "WHERE [z_Basis_QSReport5_Proposal Details].CriteriaFY IN('" & strCriteria & "');"

Also, you will need to close your query before you can run it. So qdf.close is required before the docmd.openquery().