Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2013

    Unanswered: VBA code running query error 3141

    I am attempting to use the following code in the AfterUpdate event of an unbound control on a form. The form is to record payments of invoices. The user will enter the amount of a received cheque (in another textbox) and the invoice number being paid in [txtInvoiceNumber]. I hope to use the code to validate the invoice amount against the cheque amount. strSQL is copy/pasted from a query. If the form is open and txtInvoiceNumber populated, the query returns the correct amount of the invoice. The code, when run, returns run time error 3141 "the Select statement includes a reserved word, misspelling or punctuation"
    Any help greatly appreciated
    Access 2013 *.mdb file

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT Sum([klaTotal]) AS InvoiceKLATotal" & _
    "FROM (SELECT DISTINCTROW tblInvoices.OrderID," & _
    "[UnitClientDisb]*[Quantity]+((([UnitKLAFees]+[UnitKLADisb]+[UnitTransfee])*[Quantity])*([gstRate]+1)) AS klaTotal" & _
    "FROM tblInvoices INNER JOIN tblInvoiceDetails ON tblInvoices.OrderID = tblInvoiceDetails.OrderID" & _
    "WHERE (((tblInvoices.OrderID)= [Forms]![frmPayments]![txtInvoiceNumber]))) AS InvoiceKLADetail;"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    Set db = Nothing
    Set rs = Nothing

  2. #2
    Join Date
    Jun 2013
    Solved! There were no spaces before the double quotes on each line of the SQL stmt I also had to change the form reference to a variable assigned to the text box

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    1. Several spaces are missing:
    SELECT Sum([klaTotal]) AS InvoiceKLATotalFROM ...
    ...*([gstRate]+1)) AS klaTotalFROM tblInvoices...
    ...tblInvoiceDetails.OrderIDWHERE (((...
    2. The FROM part is probably incorrect (not sure without testing).
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts