When i execute the following code i get a Run time error '3075': Syntax error (missing operator) in query expression 'Qry_OrderTotalDue.OrderID_FK =' , with the line Set myrs = mydb.OpenRecordset(sql) highlighted.
Code:Private Sub CheckBalance() Dim sql As String Dim mydb As Database Dim myrs As DAO.Recordset Set mydb = CurrentDb sql = " SELECT Qry_OrderTotalDue.OrderID_FK, Qry_OrderTotalDue.TotalDue, Qry_OrderTotalPayment.TotalPaid, [TotalDue]-[TotalPaid] AS Balance " & _ " FROM Qry_OrderTotalDue LEFT JOIN Qry_OrderTotalPayment ON Qry_OrderTotalDue.OrderID_FK = Qry_OrderTotalPayment.OrderID_FK " & _ " WHERE Qry_OrderTotalDue.OrderID_FK = " & Me.OrderID_FK & ";" Set myrs = mydb.OpenRecordset(sql) If myrs.EOF = False Then [Forms]![FRM_POS].Form.TxtOrderBalance = myrs!Balance End If myrs.Close Set myrs = Nothing Set mydb = Nothing End Sub
so i thought maybe i should check for null value in the parameter of the WHERE clause. Therefore, i came up with the following code:
NOTE: OrderID_FK is a numeric dataypeCode:Private Sub CheckBalance() Dim sql As String Dim mydb As Database Dim myrs As DAO.Recordset Set mydb = CurrentDb sql = " SELECT Qry_OrderTotalDue.OrderID_FK, Qry_OrderTotalDue.TotalDue, Qry_OrderTotalPayment.TotalPaid, [TotalDue]-[TotalPaid] AS Balance " & _ " FROM Qry_OrderTotalDue LEFT JOIN Qry_OrderTotalPayment ON Qry_OrderTotalDue.OrderID_FK = Qry_OrderTotalPayment.OrderID_FK " & _ " WHERE [Qry_OrderTotalDue.OrderID_FK] = " & Nz(Me.OrderID_FK, 0) & ";" Set myrs = mydb.OpenRecordset(sql) If myrs.EOF = False Then [Forms]![FRM_POS].Form.TxtOrderBalance = myrs!Balance End If myrs.Close Set myrs = Nothing Set mydb = Nothing End Sub
But still the same error. What am i missing?