Label not showing on subform if value is 0 (1 Viewer)

[arnelgp]

and try using "*" rather than "1" in the dcount function

Actually I have read this long time on one of my Access books.
I am too lazy to look through all my numerous books, so I
asked AI.

Here is what AI has to say:

Question:
Can i use "1" instead of "*" in Dcount domain function in ms access?

Answer:
Yes, you can use "1" instead of "*" in DCount, and it's actually more efficient!

Both work the same way:

vba
' These are equivalent:
DCount("*", "Customers")
DCount("1", "Customers")

' With criteria:
DCount("*", "Orders", "Status='Active'")
DCount("1", "Orders", "Status='Active'")
Why "1" is better:

Faster - Access doesn't need to resolve field names
No field name conflicts - Won't break if you rename/delete fields
Clearer intent - You're just counting records, not referencing actual data
Performance comparison:

vba
' Slower - Access must validate field exists
DCount("*", "LargeTable", "Status='Active'")

' Faster - Just counts records
DCount("1", "LargeTable", "Status='Active'")

' Also works with specific field (counts non-null values)
DCount("CustomerID", "Orders") ' Only counts non-NULL CustomerIDs
Best practices:

Use "1" when counting all records
Use "FieldName" when you want to count only non-NULL values in that field
Use "*" if you prefer traditional SQL syntax
Example:

vba
' Count all customers
Debug.Print DCount("1", "Customers") ' Recommended

' Count customers with email
Debug.Print DCount("Email", "Customers") ' Counts non-NULL emails

' Count active orders
Debug.Print DCount("1", "Orders", "Status='Active'") ' Best performance
So yes, use "1" - it's a better choice!

 

[CJ_London]

can't say I agree with all those comments

Why "1" is better:

Faster - Access doesn't need to resolve field names
No field name conflicts - Won't break if you rename/delete fields
Clearer intent - You're just counting records, not referencing actual data
Performance comparison:

you don't do any of those things using "*" either

comparisons using a field name, ditto

I didn't say don't use 1, just suggested trying * instead. So no need to get defensive. I would point out you can name a field 1 (or *) so that could confuse the issue

 

[Gasman]

Since when did we believe everything AI tells us?
Needs to be taken with a pinch of salt.

I had not seen using 1 before, and was suprised it worked. I would still use *. How may milliseconds would we be saving?

 

[arnelgp]

Since when did we believe everything AI tells us?

Needs to be taken with a pinch of salt.

I already told, that I have actually read this a long time ago by a book of an MVP (actual person not AI), and been using it since (here is a sample of my use: https://www.access-programmers.co.u...-duplicates-giving-error.334387/#post-1969263 and agan here: https://www.access-programmers.co.u...ecirds-from-query-result.334252/#post-1966690) and many more instance on this forum (search DLookup("1")).

and again in 2022: https://www.access-programmers.co.u...ria-in-a-recordset-clone.324934/#post-1845938

 

[Gasman]

Did you get your neat trick with DlookUp(), retrieving more than one value, from that book as well?

 

[arnelgp]

here is a comparison test using more than 1M records (but I doubt you will download it).

demo dcount("1")

 

[Gasman]

here is a comparison test using more than 1M records (but I doubt you will download it).

demo dcount("1")

Well you would be wrong.

Handy table to have for any large dataset tests as well. Thank you.

Results for everyone else.

DCount("*", "SalesRecord")
Result: 1048575 in 0.22265625s

DCount("Country", "SalesRecord")
Result: 1048575 in 3.1796875s

DCount("1", "SalesRecord")
Result: 1048575 in 0.03125s

=== With Criteria ==============
DCount("*", "SalesRecord", "Region='Europe'")
Result: 271903 in 0.42578125s

DCount("Country", "SalesRecord", "Region='Europe'")
Result: 271903 in 0.45703125s

DCount("1", "SalesRecord", "Region='Europe'")
Result: 271903 in 0.41796875s

Seems any criteria levels things? Certainly a lot faster if no criteria.
I wonder what Foxpro would have done it in?

 

[cheekybuddha]

Faster - Access doesn't need to resolve field names
No field name conflicts - Won't break if you rename/delete fields
Clearer intent - You're just counting records, not referencing actual data
Performance comparison:

vba
' Slower - Access must validate field exists
DCount("*", "LargeTable", "Status='Active'")

This is counter-intuitive (I'm not saying it is not true!)

The * here does not require the db engine to enumerate any fields - this seems like an AI mis-understanding. It is not the same as SELECT * ...

Using * generally allows the COUNT() query to use the table metadata statistics for record count, rather than actually perform a full table scan to get the result.

When a WHERE clause is in effect, then the result is dependent on if the condition field is indexed.

I have not performed any tests, so it may be the case that SELECT 1 performs better, but I am suspicious of the reasons given by the AI.

 

[cheekybuddha]

Ah, I missed the tests - thank you @Gasman and @arnel for showing the benefit of SELECT 1

 

[xavier.batlle]

I'd like to add that if there are Null values in the column you are counting you may get unexpected or unwanted values.

DCount("*", "SalesRecord")
Result: 1048575 in 0,015625s

DCount("Country", "SalesRecord")
Result: 1048570 in 0,125s

DCount("1", "SalesRecord")
Result: 1048575 in 0s

 

[xavier.batlle]

I've tested the arnelgp code indexing on the Region field and it's surprising how some Dcount() improve their performance and others don't.

=== Withoud indexing==============
DCount("*", "SalesRecord", "Region='Europe'")
Result: 271901 in 0,14453125s

DCount("Country", "SalesRecord", "Region='Europe'")
Result: 271901 in 0,16015625s

DCount("1", "SalesRecord", "Region='Europe'")
Result: 271901 in 0,140625s


=== Indexing on Region ==============
DCount("*", "SalesRecord", "Region='Europe'")
Result: 271901 in 0,00390625s

DCount("Country", "SalesRecord", "Region='Europe'")
Result: 271901 in 0,15625s

DCount("1", "SalesRecord", "Region='Europe'")
Result: 271901 in 0,00390625s