Multi Select Listbox Access 2003 Code gives syntax error in Ms Access 2010

[papic1972]

Hi All,

I've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:

My code is:

On Error GoTo Err_Command151_Click

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String


' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelectContractSummary3")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List58.ItemsSelected
strCriteria = strCriteria & ".'" & Me!List58.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Msg from ZP!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM qryContractListSummarybyDateContract3TYPEBREAK " & _
"WHERE qryContractListSummarybyDateContract3TYPEBREAK.ReportableName IN(" & strCriteria & ");"



' Apply the new SQL statement to the query
qdf.SQL = strSQL


' Open the query
DoCmd.OpenReport "rptMarginReportSummary4", acPreview, , strSQL

' Empty the memory
Set db = Nothing
Set qdf = Nothing

Exit_Command151_Click:
Exit Sub
Err_Command151_Click:
MsgBox Err.Description
Resume Exit_Command151_Click
End Sub


The syntax error I get in Access 2010 is:

Syntax Error in query expression 'SELECT * FROM
qryContractListSummarybyDateContract3TYPEBREAK WHERE
qryContractListSummarybyDateContract3TYPEBREAK.ReportableName IN('Adbri
Masonry NSW');'

Can anyone please help?

Thanks!

 

[MLUCKHAM]

You cannot have a space in a field name with square brackets. So Rep ortableName needs to be [Rep ortableName]. That would be my guess...

 

[papic1972]

Hi MLUCKHAM,

Thanks for that but that was one of the first things I tried & it still gives me the same syntax error.

 

[MLUCKHAM]

OK, can you post the error showing the square brackets around as it would never run the way you have written it above. That space is just wrong in that field. Humour me - run it again but with this code: -

strSQL = "SELECT * FROM qryContractListSummarybyDateContract3TYPEBREAK " & _
"WHERE qryContractListSummarybyDateContract3TYPEBREAK.[Rep ortableName] IN(" & strCriteria & ");"

 

[papic1972]

OK, I've run this code in Access 2003 for the past 10 years without ever having a problem.

I've tried your code, and it gives me the following syntax error:

Syntax Error in query expression 'SELECT * FROM
qryContractListSummarybyDateContract3TYPEBREAK WHERE
qryContractListSummarybyDateContract3TYPEBREAK.[ReportableName]
IN('Adbri Masonry NSW');'.

 

[MLUCKHAM]

In the code above you have no longer got the space you had in your original post for the word ReportableName. Check your table - is the field [Rep ortableName] like it was in your original post or is it [ReportableName] like it is in the above post?

If it is [ReportableName] then try changing the code to this :-
strSQL = "SELECT * FROM qryContractListSummarybyDateContract3TYPEBREAK " & _
"WHERE [ReportableName] IN(" & strCriteria & ");"

If you still get the error then you need to post the sql for the query qryContractListSummarybyDateContract3TYPEBREAK.

 

[papic1972]

I can see what you are referring to in my original post. I don't know why it posted with the space because I definitely do NOT write code that way, I've been doing this way too long!

The error is still happening, my sql for qryContractListSummarybyDateContract3TYPEBREAK is:

SELECT qryContractListSummarybyDateContract2TYPEBREAK.DeliveryDate, qryContractListSummarybyDateContract2TYPEBREAK.Contract, qryContractListSummarybyDateContract2TYPEBREAK.ReportableName, IIf([BrickType2]="TF","1",IIf([BrickType2]="N","2",IIf([BrickType2]="Other","5",IIf([BrickType2]="DTS","4",IIf([BrickType2]="DUL","4",3))))) AS SortOrder, qryContractListSummarybyDateContract2TYPEBREAK.BrickType2, qryContractListSummarybyDateContract2TYPEBREAK.ContractID, qryContractListSummarybyDateContract2TYPEBREAK.SumOfQty, IIf([BrickType2]="Other",0,[SumofQty]) AS SumofQty2, qryContractListSummarybyDateContract2TYPEBREAK.SumOfMinQty, IIf([BrickType2]="Other",0,[SumofMinQty]) AS SumofMinQty2, qryContractListSummarybyDateContract2TYPEBREAK.SumOfJDPayment, qryContractListSummarybyDateContract2TYPEBREAK.SumOfSubPayment, qryContractListSummarybyDateContract2TYPEBREAK.SumOfBalance, qryContractListSummarybyDateContract2TYPEBREAK.SumOfAmountPaid1, qryContractListSummarybyDateContract2TYPEBREAK.SumOfAmountPaid2, qryContractListSummarybyDateContract2TYPEBREAK.SumOfCredits, qryContractListSummarybyDateContract2TYPEBREAK.TotalAmountPaid, ([SumofJDPayment]-[TotalAmountPaid]) AS TotalOutstanding
FROM qryContractListSummarybyDateContract2TYPEBREAK
ORDER BY qryContractListSummarybyDateContract2TYPEBREAK.DeliveryDate, qryContractListSummarybyDateContract2TYPEBREAK.Contract, IIf([BrickType2]="TF","1",IIf([BrickType2]="N","2",IIf([BrickType2]="Other","5",IIf([BrickType2]="DTS","4",IIf([BrickType2]="DUL","4",3)))));

 

[MLUCKHAM]

Have you tried injecting your where clause into the above query to see if you get the same error? Can you post the sql for qryContractListSummarybyDateContract2TYPEBREAK as ReportableName is a field of this query? You need to watch the nesting of queries in 2010 as I have found it is pretty poor (but the error is normally "the query is too complex").

 

[CJ_London]

Is this another pasting error? Missing space after IN?

.... IN(" & strCriteria & ");"

 

[Galaxiom]

Note that the forum automatically inserts a space after a certain number of characters to ensure wrapping otherwise on some screen it can screw up all posts being displayed.

Always post code between code tags so it is displayed in a box. The code box also uses a font that is better suited to code display.

 

[papic1972]

yes it is another pasting error CJ London! I've never had this problem before today!
Thanks Galaxiom!

OK, I don't get any error when I run the query directly, only through the code on the cmd button.

The SQL for qryContractListSummarybyDateContract2TYPEBREAK is:

SELECT qryContractListSummarybyDateContractBREAK.ContractID, qryContractListSummarybyDateContractBREAK.DeliveryDate, qryContractListSummarybyDateContractBREAK.Contract, qryContractListSummarybyDateContractBREAK.ReportableName, qryContractListSummarybyDateContractBREAK.BrickType2, Sum(qryContractListSummarybyDateContractBREAK.Qty) AS SumOfQty, Sum(qryContractListSummarybyDateContractBREAK.MinQty) AS SumOfMinQty, Sum(qryContractListSummarybyDateContractBREAK.JDPayment) AS SumOfJDPayment, Sum(qryContractListSummarybyDateContractBREAK.SubPayment) AS SumOfSubPayment, Sum(qryContractListSummarybyDateContractBREAK.Balance) AS SumOfBalance, Sum(qryContractListSummarybyDateContractBREAK.AmountPaid1) AS SumOfAmountPaid1, Sum(qryContractListSummarybyDateContractBREAK.AmountPaid2) AS SumOfAmountPaid2, Sum(qryContractListSummarybyDateContractBREAK.Credits) AS SumOfCredits, Sum(([AmountPaid1]+[AmountPaid2])-[Credits]) AS TotalAmountPaid
FROM qryContractListSummarybyDateContractBREAK
GROUP BY qryContractListSummarybyDateContractBREAK.ContractID, qryContractListSummarybyDateContractBREAK.DeliveryDate, qryContractListSummarybyDateContractBREAK.Contract, qryContractListSummarybyDateContractBREAK.ReportableName, qryContractListSummarybyDateContractBREAK.BrickType2;

 

[MLUCKHAM]

I do recall having an issue like this on a nested query in Access. I never really got to the bottom of it other than I suspected it was down to the "New" ACE database engine (which was started from 2007 onwards) compiling the query and cocking up the syntax due to query length...

My advice would be to look at the nesting to possibly simplify. If I was writing your query in SQL Server I would be using a temp table to speed up the execution. Not sure how many rows qryContractListSummarybyDateContract3TYPEBREAK generates, but if the query is not too heavy duty (e.g. creates lots of rows) I would create a pseudo "temporary" table in Access and then run your criteria against the temporary table of records.

So, in summary, the workaround I suggest would be: -

Create a Table of the same shape as the
qryContractListSummarybyDateContract3TYPEBREAK query.

Start the process by clearing down this table each and every time you run your code.
Add code to insert the results of qryContractListSummarybyDateContract3TYPEBREAK into this table.
The query the results table with your criteria.

It should still be pretty zippy as long as qryContractListSummarybyDateContract3TYPEBREAK is not creating more than 10,000 or so (as you will be writing that much data each and every time you run your code).

Also, lots of big writes and deletes will result in your database getting full of empty space so again this technique will not work if qryContractListSummarybyDateContract3TYPEBREAK generates too many records...

 

[papic1972]

Thank you MLUCKHAM, I've taken you what you have said on board and try to simplify the nested query. Thanks heaps!!