Problem with Null value elimintation criteria

[Gerry]

Access 2007 on Vista.

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));

The error message I receive is, "You are trying to execute a query that does
not include the expression 'Not qryAgentsImport.RecAgentID Is Null And
tblAgents.AgentID Is Null' as part of an aggregate function.

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.


Here's the simpler SQL (not eliminating entries where RecAgentID is Null)
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));

 

[KARL DEWEY]

Backup your database -- Try this --
INSERT INTO tblAgents ( AgentID, AgentName )
SELECT qryAgentsImport.RecAgentID, qryAgentsImport.RecAgentName
FROM tblAgents RIGHT JOIN qryAgentsImport ON tblAgents.AgentID =
qryAgentsImport.RecAgentID
WHERE (qryAgentsImport.RecAgentID Is Not Null) AND
(tblAgents.AgentID Is Null);