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

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.

Continue reading...