which is Also a Constant and not a Fieldname, don't you get it?
using a Field, Access will Evaluate it if it is Null Or Not first, that is why the delay.
while using a Constant, access will evaluate Once.
It is certainly true that using the wildcard * in the DCount expr clause means that all records are counted (including any nulls)
The following quote is from
DCount Function (microsoft.com)
The DCount function doesn't count records that contain Null values in the field referenced by expr unless expr is the asterisk (*) wildcard character.
If you use an asterisk, the DCount function calculates the total number of records, including those that contain Null fields.
I certainly wouldn't call the wildcard * a 'constant'. It would appear that using e.g. "1" has the same effect
Nevertheless you are completely ignoring the effect of using an
indexed field such as ID in the
DCount expression.
As a test I updated 10000 records in the Region field to Null leaving 990,000 records with values
So as expected, DCount ("Region"....) gave a count of 990000 when I ran Test 1
I then indexed the Region field and repeated the test. The count was still 990000 as you would expect ....but it was just as fast as Tests 2,3 & 4
Each test was run 100 times on a loop. Here are the results:
Test 1: Non Indexed Region Field Count completed. Total record count = 990000: Time taken = 35.3125 seconds
Test 1: Indexed Region Field Count completed. Total record count = 990000: Time taken = 1.671875 seconds
Test 2: 'Constant' Count completed. Total record count = 1000000: Time taken = 1.66015625 seconds
Test 3: Indexed ID Field Count completed. Total record count = 1000000: Time taken = 1.65625 seconds
Test 4: DCount* completed. Total record count = 1000000: Time taken = 1.66015625 seconds
This is exactly as I originally wrote back in post #2
If the search field is indexed, this should be very fast even with 1 million records.