Can an append query use the count function to add only a limited number of records?

[the_vba_noob]

I have created a simple Append query in the Access gui which attempts to use the Count() function.
An error is produced which states "Cannot have aggregate function in WHERE clause"

For Criteria, I had set the following:

Please Login or Register  to view this content.

Given that every occurence of "MyField" is always greater than zero, I was attempting to establish a record count which I had hoped would update as the Append query executed.
I had reasoned that once a user-specified record count such as "1000000" had been reached, the Append query would stop.
I am trying to avoid exceeding the maximum size of a Database in Access.

I now understand that the Count function is an aggregate function which is not permitted inside a WHERE clause.

Is there a better way to tell the Append query to stop appending records after a user-specified number of records have already been appended?

[ranman256]

a query cannot sum AND append in the same query.
you must break it in 2, either:
1. post the sums to a temp table,then append that table to target
or
2. use DSum() in the append query to add the sum