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.
[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.