SQL string throws "External Name not Defined" error when running in VBA module - Name is defined

Question

In Access Query window the SQL code runs fine and gives accurate results, however, when I transfer the code to the VBA module, I receive an "External Name Not Defined" error on the [tblAssociate].[AssocID] in the DMax function.

The name is defined so I'm not sure where the issue is?

SELECT 
    tblAssociate.AssocID, 
    tblAssociate.PersNo, 
    tblAssociate.Inactive, 
    
    " & DLookup("[Position]", "tblAssocHistory", "[AssocHistoryID] = " & DMax("[AssocHistoryID]", "tblAssocHistory", "[AssocID] = " & [tblAssociate].[AssocID])) & " AS CurrPos, 
    
    tblAssignments.Inactive, 
    tblAssignments.AssignmentID 
    
    FROM tblAssociate 
    INNER JOIN tblAssignments ON tblAssociate.AssocID = tblAssignments.AssocID WHERE 
    (
     ((tblAssociate.Inactive)=True) AND (tblAssignments.Inactive = False)
    ) 
    OR 
    (
      (tblAssociate.Inactive = False) 
      AND 
      (
       " & DLookup("[Position]", "tblAssocHistory", "[AssocHistoryID] = " & DMax("[AssocHistoryID]", "tblAssocHistory", "[AssocID] = " 
       & [tblAssociate].[AssocID])
      ) & " Not In 
       (SELECT ref_Config.ConfigLongDesc FROM ref_Config WHERE ref_Config.ConfigShortDesc In ('Agent Positions')
       )
      ) 
      AND tblAssignments.Inactive = False
     )"

The error appears on both instances of the DMax Where clause.

This code string should provide a listing of assignments to be cancelled because the agent is no longer an active associate.

Answer 1

[tblAssociate].[AssocID] is not defined until after the DLookup is performed and concatenated into the SQL statement. You cannot use values from the query within DLookups that are concatenated into the SQL. I suggest you consider SQL joins.