CODEWITHSUNDEEP
sqlms-access
Марко Фернандез
Марко Фернандез

Reputation: 1

Invalid bracketing of name

I'm getting an error Invalid bracketing of name [qryTemp.IEnduseID]

strSQL = "SELECT tbl_Item_Enduse.IEnduseID, tbl_Item_Enduse.IEnduseDesc, IIf([qryTemp.IEnduseID]>0,-1,0) AS Sel " & _
    "FROM tbl_Item_Enduse LEFT JOIN (SELECT tbl_ItemsEU.EUid FROM tbl_ItemsEU " & _
    "WHERE tbl_ItemsEU.ItemID=" & Nz(Me.ItemID, 0) & ")  AS qryTemp " & _
    "ON tbl_Item_Enduse.IEnduseID = qryTemp.EUid " & _
    "ORDER BY tbl_Item_Enduse.IEnduseDesc;"

If I remove the bracket - I'm getting 'Too few parameters ...

Please help.

Upvotes: 0

Views: 1070

Answers (2)

Parfait
Parfait

Reputation: 107652

From your JOIN expression, consider using EuId instead of IEnduseID since former is referenced in subquery you aliased as qdyTemp. Otherwise, referencing the latter column will result in unknown paramters. Consider also, shorter table aliases for compact readability.

strSQL = "SELECT t.IEnduseID, t.IEnduseDesc, " & _
         "       IIf([q.EUid]>0,-1,0) AS Sel " & _ 
         "FROM tbl_Item_Enduse AS t" & _
         "LEFT JOIN " & _
         " (SELECT sub.EUid " & _
         "  FROM tbl_ItemsEU sub " & _ 
         "  WHERE sub.ItemID=" & Nz(Me.ItemID, 0) & ") AS q " & _ 
         "ON t.IEnduseID = q.EUid " & _ 
         "ORDER BY t.IEnduseDesc;"

By the way, not sure how you are using above dynamic query from SQL string, but consider a saved query that will be more efficient with JOIN as engine saves best execution plan. Also, saved queries can directly use open form controls. Doing so you also avoid messy VBA concatenation.

SELECT t.IEnduseID, t.IEnduseDesc,
       IIF([q.EUid]>0,-1,0) AS Sel
FROM tbl_Item_Enduse AS t
LEFT JOIN
     (SELECT sub.EUid
      FROM tbl_ItemsEU sub
      WHERE sub.ItemID = Nz(Forms!myOpenForm!ItemID, 0) AS q
ON t.IEnduseID = q.EUid
ORDER BY t.IEnduseDesc;

In VBA, stored queries with GUI parameters can be used in multiple ways involving GUI operations (i.e., forms, reports).

DoCmd.OpenQuery "mySavedQuery"

Me.Form.RecordSource = "mySavedQuery"
Me.Form.Requery

Me.myComboOrListBox.RowSource = "" 
Me.myComboOrListBox.RowSource = "mySavedQuery"
Me.myComboOrListBox.Requery

Upvotes: 1

Gustav
Gustav

Reputation: 55841

You are mixing up field names.

Try with (just guessing):

strSQL = "SELECT tbl_Item_Enduse.IEnduseID, tbl_Item_Enduse.IEnduseDesc, IIf(qryTemp.IEnduseID>0,-1,0) AS Sel " & _
    "FROM tbl_Item_Enduse LEFT JOIN (SELECT tbl_ItemsEU.EUid As IEnduseID FROM tbl_ItemsEU " & _
    "WHERE tbl_ItemsEU.ItemID=" & Nz(Me.ItemID, 0) & ")  AS qryTemp " & _
    "ON tbl_Item_Enduse.IEnduseID = qryTemp.IEnduseID " & _
    "ORDER BY tbl_Item_Enduse.IEnduseDesc;"

Upvotes: 0

Related Questions