need help with syntax (1 Viewer)

[hfs]

Hi there,
I m trying to write this query in vba code but it is constantly giving

Compile Error
Syntax error !

Private Sub cboTaskListName_AfterUpdate()
 
Dim db As DAO.Database
Dim SQL As String
 
 If Me.cboTaskListName = "**All**" Then
 
 Set db = CurrentDb()
 SQL = "SELECT tblDiscrepancy.DiscrepancyID, tblArea.AreaDesc, tblAuditGroup.AuditGroupDescription,tblObservedHeader.ObservedDate , tblDiscrepancy.DiscrepancyDesc, tblDiscrepancy.DiscrepancyVerifiedDate,tblDiscrepancy.DiscrepancyNote , tblObservedHeader.ObservedID, tblObservedHeader.ObservedBy, tblObservedHeader.ObservedArea,tblDiscrepancy.DiscrepancyAssignedTo , tblDiscrepancy.DiscrepancyVerifiedBy FROM (tblArea RIGHT JOIN (tblAuditGroup RIGHT JOIN tblObservedHeader ON tblAuditGroup.[AuditGroupID] = tblObservedHeader.ObservedBy) ON tblArea.AreaID = tblObservedHeader.ObservedArea) RIGHT JOIN (tblAuditStaff AS tblAssignedTo RIGHT JOIN (tblAuditStaff AS tblVerifiedBy RIGHT JOIN tblDiscrepancy ON tblVerifiedBy.AuditStaffID = tblDiscrepancy.DiscrepancyVerifiedBy) ON tblAssignedTo.AuditStaffID = tblDiscrepancy.DiscrepancyAssignedTo) ON tblObservedHeader.ObservedID = tblDiscrepancy.DiscrepancyTest
WHERE (((tblDiscrepancy.DiscrepancyOngoing) = False) And ((tblDiscrepancy.DiscrepancyNonGMP) = False) And ((tblDiscrepancy.DiscrepancyCompleted) = False) And ((tblDiscrepancy.DiscrepancyDuplicate) = False) And ((tblDiscrepancy.DiscrepancyCorrectiveAction) Is Not Null) And ((tblDiscrepancy.DiscrepancyTargetCompDate) Is Not Null) And ((tblDiscrepancy.DiscrepancyActualCompDate) Is Null))ORDER BY tblArea.AreaDesc"
  End If
 
End Sub

 

[The_Doc_Man]

I'm not about to decipher that string, but I'll tell you how to do what you want.

Go into query design grid. In the upper portion, put in the tables you need. TEMPORARILY assign relationships that lead to your right-join. Pull in the fields you need. Now save that query to an arbitrary name. (You will delete it when you are done anyway so call it FRED for all I care.) Switch to SQL Design mode. Now do a COPY/PASTE of the query string. If you can build the query at all on the grid, you will not have a syntax error when you are done.

However, I'll also offer a note of advice on reading that mess you posted. (Sorry, but posting long strings that overflow lines qualifies as a hot mess anywhere.) Part of your problem is confusion factor.

If you have a long string that looks like this:

"SELECT tblDiscrepancy.DiscrepancyID, tblArea.AreaDesc, tblAuditGroup.AuditGroupDescription,  ...

Consider breaking it up like this just to make it readable.

"SELECT tblDiscrepancy.DiscrepancyID, tblArea.AreaDesc, " & _
"tblAuditGroup.AuditGroupDescription, " & _ ...

Breaking it up like that will not hurt anything and will help you see what you are doing one or two fields at a time. Your syntax error is probably hiding amidst clutter. So always consider decluttering.

 

[hfs]

Thankyou so much for your valuable advice.As i am new in access ,so have no idea how to deal with errors .THanks Again

 

[pr2-eugin]

What is that you are trying to do? If the Syntax error occurs as a possibility of improper JOIN we will not be help you.. As only you and you alone know the structure of the DB.. So try Debugging the code..

Private Sub cboTaskListName_AfterUpdate()
    Dim db As DAO.Database
    Dim strSQL As String
    If Me.cboTaskListName = "**All**" Then
        Set db = CurrentDb()
        strSQL = "SELECT tblDiscrepancy.DiscrepancyID, tblArea.AreaDesc, tblAuditGroup.AuditGroupDescription, " & _
              "tblObservedHeader.ObservedDate, tblDiscrepancy.DiscrepancyDesc, tblDiscrepancy.DiscrepancyVerifiedDate, " & _
              "tblDiscrepancy.DiscrepancyNote , tblObservedHeader.ObservedID, tblObservedHeader.ObservedBy,  " & _
              "tblObservedHeader.ObservedArea,tblDiscrepancy.DiscrepancyAssignedTo , tblDiscrepancy.DiscrepancyVerifiedBy " & _
              "FROM (tblArea RIGHT JOIN (tblAuditGroup RIGHT JOIN tblObservedHeader ON tblAuditGroup.[AuditGroupID] = tblObservedHeader.ObservedBy) " & _
              "ON tblArea.AreaID = tblObservedHeader.ObservedArea) RIGHT JOIN (tblAuditStaff AS tblAssignedTo RIGHT JOIN " & _
              "(tblAuditStaff AS tblVerifiedBy RIGHT JOIN tblDiscrepancy ON tblVerifiedBy.AuditStaffID = tblDiscrepancy.DiscrepancyVerifiedBy) " & _
              "ON tblAssignedTo.AuditStaffID = tblDiscrepancy.DiscrepancyAssignedTo) ON " & _
              "tblObservedHeader.ObservedID = tblDiscrepancy.DiscrepancyTest WHERE (((tblDiscrepancy.DiscrepancyOngoing) = False) " & _
              "And ((tblDiscrepancy.DiscrepancyNonGMP) = False) And ((tblDiscrepancy.DiscrepancyCompleted) = False) And  " & _
              "((tblDiscrepancy.DiscrepancyDuplicate) = False) And ((tblDiscrepancy.DiscrepancyCorrectiveAction) Is Not Null) " & _
              "And ((tblDiscrepancy.DiscrepancyTargetCompDate) Is Not Null) And ((tblDiscrepancy.DiscrepancyActualCompDate) " & _
              "Is Null))ORDER BY tblArea.AreaDesc;"
        Debug.Print strSQL
      End If
End Sub