Ms Access does not recognize as a Valid field name

Question

TRANSFORM IIF(IsNull(First(wrhCode)),'' ,First(wrhCode) & ', '  ) AS FirstOfwrhCode 
   SELECT whiitemID AS ItemID, 
          whiItemName AS Name, 
          Sum(Qty) AS Quantity
     FROM (SELECT wrhID,  
                  wrhCode, 
                  whiitemID,
                  whiItemName,
                  Sum(whiQty) AS QTY 
            FROM tblWarehouseItem AS WI 
                 INNER JOIN tblWarehouse AS W ON WI.whiwrhID = W.wrhID
           WHERE whiActive = True                           
             AND whiwrhID IN (Forms.frmStockControl.Form.txtwrhIDs) 
        GROUP BY wrhID, wrhCode, whiItemName, whiitemID)  AS [%$##@_Alias]
 GROUP BY whiitemID, whiItemName
    PIVOT 'wrhID' & wrhID;

I have this query to my form but it does not work: But if i edit :

 AND whiwrhID IN (Forms.frmStockControl.Form.txtwrhIDs)

to:

 AND whiwrhID IN (26,27,29)

Is working any ideas how to fix the error

Do you receive an "error" or is just not working? – luk2302 May 05 '15 at 11:40 — luk2302, May 05 '15 at 11:40
Did you try `Forms!frmStockControl!txtwhrIDs`? – ZX9 May 05 '15 at 16:32 — ZX9, May 05 '15 at 16:32

score 1 · Answer 1 · answered May 05 '15 at 11:51

1

You can't.

    whiwrhID IN (26,27,29)

must be hard coded in SQL.

Your only option is to adjust the SQL before calling the query.

answered May 05 '15 at 11:51

Gustav

53,498
7
29
55

score 1 · Answer 2 · edited May 23 '17 at 12:14

Why not use VBA to build a dynamic query?

You can initiate a sub MakeAQuery through an event or macro to modify your IN statement to whatever you need:

Sub MakeAQuery()
    Dim db As DAO.Database
    Dim strSQL As String
    Dim strElements As String

    Set db = CurrentDb()

'Your current SQL with a parameter in the IN clause
    strSQL = "TRANSFORM IIF(IsNull(First(wrhCode)),'' ,First(wrhCode) & ', '  ) _
    AS FirstOfwrhCode _
    SELECT whiitemID AS ItemID, _
      whiItemName AS Name, _
      Sum(Qty) AS Quantity _
    FROM (SELECT wrhID, _
              wrhCode, _
              whiitemID, _
              whiItemName, _
              Sum(whiQty) AS QTY _
        FROM tblWarehouseItem AS WI _
             INNER JOIN tblWarehouse AS W ON WI.whiwrhID = W.wrhID _
       WHERE whiActive = True _                  
         AND whiwrhID In ({0})_
       GROUP BY wrhID, wrhCode, whiItemName, whiitemID) _ 
    AS [%$##@_Alias]
    GROUP BY whiitemID, whiItemName
    PIVOT 'wrhID' & wrhID;"

    ' Retrieve your IDs for the IN clause
    strElements = Forms.frmStockControl.Form.txtwrhIDs

    ' Assign SQL to query
    db.QueryDefs!YourQuery.SQL = Replace(strSQL, "{0}", strElements)

End Sub

To assign this VBA sub to a button or other control on your form...

Go to to Layout View for your form.
Clicking on a control, go to the Event tab on the Property Sheet.
Hit the ... button beside the On Click property. (You could do this for any event property; OnClick works when you click on the control.)
Choose Code Builder and Ok.
Put Call MakeTheQuery in the sub that it builds for you.

score -1 · Answer 3 · edited May 05 '15 at 12:01

-1

Try this instead:

Forms.frmStockControl.Form.txtwrhIDs.Text

edited May 05 '15 at 12:01

Ziem

6,579
8
53
86

answered May 05 '15 at 11:33

Karlta05

165
10

Ms Access does not recognize as a Valid field name

3 Answers3