Syntax error (missing operator) in query expression - VBA

Question

This is my code:

    M_BKID = DMax("BK_ID", "BookingMain")
    FSQL = " UPDATE Q_HrsToBeRefund_Writeable " & _
           " SET BD_ToBeRefund = False, BD_Refunded = True, BD_RefundedRef = " & M_BKID & " " & _
           " ORDER BY BD_Dt DESC LIMIT " & M_Refunded & " "
           
    Debug.Print FSQL            ' ********************************
    DoCmd.RunSQL FSQL

I get an error

Syntax error (missing operator) in query expression '184 ORDER BY BD_Dt DESC Limit 3

Can anyone help me solve this?

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-03-21T11:50:37

MS Access definitely doesn't support LIMIT in UPDATE. I don't think it supports TOP either. But you can do:

UPDATE Q_HrsToBeRefund_Writeable as 
    SET BD_ToBeRefund = False,
        BD_Refunded = True,
        BD_RefundedRef = ?
    WHERE <primary key> IN (SELECT TOP (M_Refunded) <primary key>
                            FROM Q_HrsToBeRefund_Writeable
                            ORDER BY BD_Dt DESC
                           );

Note:

This assumes your table has a primary key.
Note the use of ?. That is an indication that you should use a parameter for this value, rather than munging the query string.
I'm not sure if the number of rows can be passed in as a parameter.

Syntax error (missing operator) in query expression - VBA

1 Answers