Search code examples
sqlms-access

ACCESS autonumber using DCount function

Newbie using Access and I am not sure what to do next ;(

I am trying to add autonumber using MS Access query. I have 2667 ASSET_TYPE records in ASSET_insert_02 query.

I am using DCount function as follows:

Number: DCount("*","ASSET_insert_02","[ASSET_TYPE]<=" & [ASSET_insert_02]![ASSET_TYPE])

But the function keeps showing #Error with the alert:

"The expression you entered as a query parameter produced this error:

"the object doesn't contain the Automation Object "TYPE01."

Any chance of solving this issue?

enter image description here

Solution

  • Ideally, avoid domain aggregates like DCount in queries, but use a subquery instead. This benefits performance, and avoids issues with string concatenation. The notable exception is that subqueries often cause queries to not be updateable, so when a query needs to be updateable, you can use DCount with proper quoting.

    As a subquery, that statement would be:

    Number: (SELECT COUNT(a.*) FROM ASSET_insert_02 a WHERE a.[ASSET_TYPE]<= [ASSET_insert_02].[ASSET_TYPE])