Error 3075-Run Query after multi select listbox is utilized

[tangerine]

I 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 [z_Basis_QSReport5_Proposal Details].SchoolName, [z_Basis_QSReport5_Proposal Details].SchoolAcronym, [z_Basis_QSReport5_Proposal Details].PIFullName, [z_Basis_QSReport5_Proposal Details].PIGWID, [z_Basis_QSReport5_Proposal Details].AppID, [z_Basis_QSReport5_Proposal Details].App_Type, [z_Basis_QSReport5_Proposal Details].Outcome, [z_Basis_QSReport5_Proposal Details].DeptCode, [z_Basis_QSReport5_Proposal Details].SponsorName, [z_Basis_QSReport5_Proposal Details].Title_Long, [z_Basis_QSReport5_Proposal Details].ProjTotal, [z_Basis_QSReport5_Proposal Details].SubmissionDate, [z_Basis_QSReport5_Proposal Details].QuarterID, [z_Basis_QSReport5_Proposal Details].LongDesc, [z_Basis_QSReport5_Proposal Details].NumDes, [z_Basis_QSReport5_Proposal Details].FYLabel, [z_Basis_QSReport5_Proposal Details].CriteriaFY, [z_Basis_QSReport5_Proposal Details].FY " & _
            "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

The fourth from the bottome line, qdf.SQL = strSQL, is highlighted

 

[sneuberg]

To debug this I suggest putting a Debug.Print strSQL before the line qdf.SQL = strSQL. Then create a query using the query builder with the results that you found in the Immediate Window. That might give you more information about what's wrong with the query.

 

[tangerine]

Thank you! but I still get the 3075 Error

Syntax error (missing operator) in query expression '[z_Basis_QSReport5_Proposal Details].FY WHERE z_Basis_QSReport4_Proposal Details.CriteriaFY IN('FY15-Q1','FY15-Q2','FY15-Q3')'.

 

[Minty]

What sneuburg is saying is this

strCriteria = Right(strCriteria, Len(strCriteria) - 1)
   strSQL = "SELECT [z_Basis_QSReport5_Proposal Details].SchoolName, [z_Basis_QSReport5_Proposal Details].SchoolAcronym, [z_Basis_QSReport5_Proposal Details].PIFullName, [z_Basis_QSReport5_Proposal Details].PIGWID, [z_Basis_QSReport5_Proposal Details].AppID, [z_Basis_QSReport5_Proposal Details].App_Type, [z_Basis_QSReport5_Proposal Details].Outcome, [z_Basis_QSReport5_Proposal Details].DeptCode, [z_Basis_QSReport5_Proposal Details].SponsorName, [z_Basis_QSReport5_Proposal Details].Title_Long, [z_Basis_QSReport5_Proposal Details].ProjTotal, [z_Basis_QSReport5_Proposal Details].SubmissionDate, [z_Basis_QSReport5_Proposal Details].QuarterID, [z_Basis_QSReport5_Proposal Details].LongDesc, [z_Basis_QSReport5_Proposal Details].NumDes, [z_Basis_QSReport5_Proposal Details].FYLabel, [z_Basis_QSReport5_Proposal Details].CriteriaFY, [z_Basis_QSReport5_Proposal Details].FY " & _
            "WHERE z_Basis_QSReport5_Proposal Details.CriteriaFY IN(" & strCriteria & ");"

[COLOR="Red"]debug.print strSql

Exit Sub[/COLOR] [COLOR="SeaGreen"] ' Remove this line once you have it working[/COLOR]

   qdf.SQL = strSQL

Then open the VBA window and check what is in the immediate window (Ctrl G will make it appear if you haven't got it visible.

 

[sneuberg]

Try putting brackets in this line as shown below

"WHERE [z_Basis_QSReport5_Proposal Details].CriteriaFY IN(" & strCriteria & ");"

 

[tangerine]

I:
-Added the brackets as suggested
-edited the code per the suggestions and it is now:

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 [z_Basis_QSReport5_Proposal Details].SchoolName, [z_Basis_QSReport5_Proposal Details].SchoolAcronym, [z_Basis_QSReport5_Proposal Details].PIFullName, [z_Basis_QSReport5_Proposal Details].PIGWID, [z_Basis_QSReport5_Proposal Details].AppID, [z_Basis_QSReport5_Proposal Details].App_Type, [z_Basis_QSReport5_Proposal Details].Outcome, [z_Basis_QSReport5_Proposal Details].DeptCode, [z_Basis_QSReport5_Proposal Details].SponsorName, [z_Basis_QSReport5_Proposal Details].Title_Long, [z_Basis_QSReport5_Proposal Details].ProjTotal, [z_Basis_QSReport5_Proposal Details].SubmissionDate, [z_Basis_QSReport5_Proposal Details].QuarterID, [z_Basis_QSReport5_Proposal Details].LongDesc, [z_Basis_QSReport5_Proposal Details].NumDes, [z_Basis_QSReport5_Proposal Details].FYLabel, [z_Basis_QSReport5_Proposal Details].CriteriaFY, [z_Basis_QSReport5_Proposal Details].FY " & _
            "WHERE [z_Basis_QSReport5_Proposal Details].CriteriaFY IN(" & strCriteria & ");"
   Debug.Print strSQL
   DoCmd.OpenQuery "z_Basis_QSReport5_Proposal Details_For_Report"
   Set db = Nothing
   Set qdf = Nothing
End Sub

YAY the error is gone. The query that results is everything: all the proposals instead of FY16-Q1 proposals as the user might have intended.

 

[Minty]

You forgot to put this back in

qdf.SQL = strSQL

And before you do that have you checked that the debug.print looks correct and works if you paste it into a new query ?

 

[sneuberg]

Please post the results in the Immediate Window, but just the results of one trial so click in the Immediate Window, Ctrl A to select all and delete before you run the code.

Edit: Try what Minty suggests first. I didn't notice that qdf.SQL = strSQL was taken out.

 

[tangerine]

OK I apologize if I seem like an idiot but I'm just not understanding this.

I used the code as listed above and YAY didn't get the error but got all the fields i needed in my query but the parameters (as listed in the form) weren't met.

I put back qdf.SQL = strSQL and the error returned.

 

[tangerine]

I'm afraid I do not know what this means.

 

[sneuberg]

Please try the following:

1. Go to the VBA Code Window
2. Open the Immediate Window Ctrl + G
3. Erase anything in the window
4. Go back to the Object Window Shift F7
5. Run the code, don't worry about the error just click end program
6. Switch to the VBA Code Window
7. Copy the results of the Immediate Window and post them so we can see them
8. Switch to the Object Window
9. IN the CREATE tab click Query Design
10. Close the Show Table dialog
11. Switch to SQL view (Upper right hand corner of ribbon or right click on the query name)
12. Paste the SQL you have on the clip board.
13. Switch to design view or datasheet view
14. Post a screen shot of what you get

 

[tangerine]

For step 7 there was nothing in the immediate window:

I can't even post a pic here because the site won't let me.

 

[sneuberg]

If the Debug.Print strSQL was before the line producing the error you should have seen something. Was it before or after?

Did you get the same error when you did step 5?

If the line qdf.SQL = strSQL is still giving you the error than comment it out and retry the steps.

 

[sneuberg]

I think you can upload after ten posts so let know us some stuff in different posts like:

What version of Access are you using?
What operating system?
What is your quest?
What is your favorite color?
What is the capital of Assyria?

 

[tangerine]

Access 2013

 

[tangerine]

I am using Windows 7

 

[tangerine]

my favorite color is purple

 

[tangerine]

thank you very much to the people helping me!

 

[tangerine]

1. Go to the VBA Code Window
2. Open the Immediate Window Ctrl + G
3. Erase anything in the window
4. Go back to the Object Window Shift F7
5. Run the code, don't worry about the error just click end program
6. Switch to the VBA Code Window
7. Copy the results of the Immediate Window and post them so we can see them
   Here they are
   

   SELECT [z_Basis_QSReport5_Proposal Details].SchoolName, [z_Basis_QSReport5_Proposal Details].SchoolAcronym, [z_Basis_QSReport5_Proposal Details].PIFullName, [z_Basis_QSReport5_Proposal Details].PIGWID, [z_Basis_QSReport5_Proposal Details].AppID, [z_Basis_QSReport5_Proposal Details].App_Type, [z_Basis_QSReport5_Proposal Details].Outcome, [z_Basis_QSReport5_Proposal Details].DeptCode, [z_Basis_QSReport5_Proposal Details].SponsorName, [z_Basis_QSReport5_Proposal Details].Title_Long, [z_Basis_QSReport5_Proposal Details].ProjTotal, [z_Basis_QSReport5_Proposal Details].SubmissionDate, [z_Basis_QSReport5_Proposal Details].QuarterID, [z_Basis_QSReport5_Proposal Details].LongDesc, [z_Basis_QSReport5_Proposal Details].NumDes, [z_Basis_QSReport5_Proposal Details].FYLabel, [z_Basis_QSReport5_Proposal Details].CriteriaFY, [z_Basis_QSReport5_Proposal Details].FY WHERE [z_Basis_QSReport5_Proposal Details].CriteriaFY IN('FY15-Q2','FY15-Q3','FY15-Q4');
   SELECT [z_Basis_QSReport5_Proposal Details].SchoolName, [z_Basis_QSReport5_Proposal Details].SchoolAcronym, [z_Basis_QSReport5_Proposal Details].PIFullName, [z_Basis_QSReport5_Proposal Details].PIGWID, [z_Basis_QSReport5_Proposal Details].AppID, [z_Basis_QSReport5_Proposal Details].App_Type, [z_Basis_QSReport5_Proposal Details].Outcome, [z_Basis_QSReport5_Proposal Details].DeptCode, [z_Basis_QSReport5_Proposal Details].SponsorName, [z_Basis_QSReport5_Proposal Details].Title_Long, [z_Basis_QSReport5_Proposal Details].ProjTotal, [z_Basis_QSReport5_Proposal Details].SubmissionDate, [z_Basis_QSReport5_Proposal Details].QuarterID, [z_Basis_QSReport5_Proposal Details].LongDesc, [z_Basis_QSReport5_Proposal Details].NumDes, [z_Basis_QSReport5_Proposal Details].FYLabel, [z_Basis_QSReport5_Proposal Details].CriteriaFY, [z_Basis_QSReport5_Proposal Details].FY WHERE [z_Basis_QSReport5_Proposal Details].CriteriaFY IN('FY15-Q2','FY15-Q3','FY15-Q4');

8. Switch to the Object Window
9. IN the CREATE tab click Query Design
10. Close the Show Table dialog
11. Switch to SQL view (Upper right hand corner of ribbon or right click on the query name)
12. Paste the SQL you have on the clip board.
13. Switch to design view or datasheet view
14. Post a screen shot of what you get

 

[sneuberg]

Thanks for the Immediate Window info but the error in the SQL is not jumping out at me. What happened in steps 8 through 14?

If you can upload now could you upload your database? That would drastically shorten this process.