Solved Need to check if there is data before requery form?

[arnelgp]

I normally use DCount ("*")

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.

 

[isladogs]

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.

 

[arnelgp]

I certainly wouldn't call the wildcard * a 'constant'. It would appear that using e.g. "1" has the same effect

it's a Constant indeed, you can place a number there and it will also count.

 

[isladogs]

FWIW, you can use a letter as well if you write the expression as e.g. DCount("""A""", "[1000000 Sales Records]").

However, I will continue to use DCount("*",...) where I need the total record count or DCount ("IndexedFieldName"...) where I need to exclude records with null values