Access 2007 - Problem with Null value elimination criteria

[Gerry]

REPOST: Prior post had a badly-chosen subject line.

I'm building a simple append query to add missing records to a table. It examines a list of entries, identifies which are not in the destination table, and adds them. Simple thus far.

The problem comes when I add a criteria to the source side to ensure no blank entries are appended.

Here's the SQL I'm trying to use:

INSERT INTO tblAgents ( AgentID, AgentName )
SELECT qryAgentsImport.RecAgentID, First(qryAgentsImport.RecAgentName) AS FirstOfRecAgentName
FROM tblAgents RIGHT JOIN qryAgentsImport ON tblAgents.AgentID = qryAgentsImport.RecAgentID
GROUP BY qryAgentsImport.RecAgentID, tblAgents.AgentID
HAVING (((qryAgentsImport.RecAgentID) Is Not Null) AND ((tblAgents.AgentID) Is Null));

Here's the simple one (not eliminating blank entries) that works fine:

INSERT INTO tblAgents ( AgentID, AgentName )
SELECT qryAgentsImport.RecAgentID, First(qryAgentsImport.RecAgentName) AS FirstOfRecAgentName
FROM tblAgents RIGHT JOIN qryAgentsImport ON tblAgents.AgentID = qryAgentsImport.RecAgentID
GROUP BY qryAgentsImport.RecAgentID, tblAgents.AgentID
HAVING (((tblAgents.AgentID) Is Null));

Here's a screen shot of the query design, with the error showing:


Interestingly, it doesn't seem to matter what criteria or field I add. Anything specifying limiting criteria on the source table seems to give me the same error message (reworded to match each criterion).



I'm sure this is something minor, but I'm a bit rusty.

[SoftwareMatters]

Instead of 'Group By' try putting 'Where' instead