Results 1 to 8 of 8
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    Why do i get run-time error 3075 on a well formatted query??

    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:

    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] = " & 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
    NOTE: OrderID_FK is a numeric dataype

    But still the same error. What am i missing?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    1. Change this line to Dim mydb As DAO.Database

    2. Add a line Debug.Print sql after the sql statement.
    Copy the output from the Immediate window into the query designer & see if it runs

    3. Change this line to Set myrs = mydb.OpenRecordset(sql, OpenDynaset)

    4. However, why use a recordset at all?
    Although DLookup isn't fast, it will probably be faster than using a recordset

    Assuming the code is being run from the form itself, use Me. notation
    Code:
    Me.TxtOrderBalance=DLookup("TotalDue", "Qry_OrderTotalPayment","OrderID_FK = " & Me.OrderID_FK) - DLookup("TotalPaid", "Qry_OrderTotalPayment","OrderID_FK = " & Me.OrderID_FK)
    HTH
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Thx for the quick reply RIDDERS52.
    Will try your solution and reply back.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Forgot to say that you should check for null values:

    Using your recordset
    Code:
    sql = " SELECT Qry_OrderTotalDue.OrderID_FK, Qry_OrderTotalDue.TotalDue, Qry_OrderTotalPayment.TotalPaid, Nz([TotalDue],0)-Nz([TotalPaid],0) 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 & ";"
    Or....
    Code:
    Me.TxtOrderBalance=Nz(DLookup("TotalDue", "Qry_OrderTotalPayment","OrderID_FK = " & Me.OrderID_FK),0) - Nz(DLookup("TotalPaid", "Qry_OrderTotalPayment","OrderID_FK = " & Me.OrderID_FK),0)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not tested, but the other possibility is that one or more of the queries your sql references could be referencing a form value as a parameter

  6. #6
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    @RIDDERS52
    thanks a lot for the suggestion. but still get the same error. Maybe i should think of another logic to achieve the goal.

    @Ajax.
    Thx you, will have a close look at my queries.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is it possible to post a copy of the database (in zip format) so readers can experience the issue in context?

  8. #8
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Well i decided to do the whole thing another way. And already implemented the changes. I Think i should have done it when posting the question. Anyway surely another time.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-10-2014, 11:30 AM
  2. Replies: 11
    Last Post: 05-01-2014, 11:56 AM
  3. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  4. Replies: 4
    Last Post: 07-25-2012, 04:01 AM
  5. Replies: 6
    Last Post: 05-30-2012, 12:32 PM

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
  •  
Other Forums: Microsoft Office Forums