DCount (1 Viewer)

Gismo

Registered User.
Local time
Today, 23:19
Joined
Jun 12, 2017
Messages
1,298
Hi All,

I need to determine the line number of a range of records in a query
I use the below code
#LineNo: DCount("*","Mate=rialNo","QTY<=" & [QTY])#
I presume I can use any control in the table that is a number field to do the count?
I used QTY as this is the only number field I have in this table

I have 12 records in this selection but my line number for each record is 15
any explanation why?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:19
Joined
Feb 19, 2013
Messages
16,553
you can count on text as well as numbers. Whatever field you count on has to be unique - if you have two identical quantities the count will be the same for both. You will also need to sort your query into the same order.
 

Gismo

Registered User.
Local time
Today, 23:19
Joined
Jun 12, 2017
Messages
1,298
when I use a text field, i get a data type mismatch error
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:19
Joined
Feb 19, 2013
Messages
16,553
for text your criteria should be

,"textfield<='" & [textfield] & "'"
 

Gismo

Registered User.
Local time
Today, 23:19
Joined
Jun 12, 2017
Messages
1,298
thank you, that seemed to have worked

i am using the part number (text field) as this will be a unique number
the result in line number is very eradicated,
it starts of at 11 and goes up to 29 skipping a few numbers in between

1589192289245.png
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:19
Joined
Feb 19, 2013
Messages
16,553
probably because either you haven't sorted the column or your query has excluded some rows which your dcount is including
 

Gismo

Registered User.
Local time
Today, 23:19
Joined
Jun 12, 2017
Messages
1,298
probably because either you haven't sorted the column or your query has excluded some rows which your dcount is including
What would be the reason why this query runs so long to allocate the line number?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:19
Joined
Feb 19, 2013
Messages
16,553
domain functions are the slowest way of doing this, particularly if the backend is not access and/or fields are not indexed. subqueries would be faster or use an aliased table.

based on your dcount function a subquery would look something like this

LineNo: (SELECT Count * FROM MaterialNo T WHERE PartNo<=MaterialNo.PartNo)
 

Gismo

Registered User.
Local time
Today, 23:19
Joined
Jun 12, 2017
Messages
1,298
Hi All,

i just can net get a grip of DCount

I now need to do a record count on a auto number field
I have 9 records in the table and my result on the count is 9 on each record, there is no increment

#LineNo: DCount("*","Material Temp File","Linenumber<=" & [Linenumber])#

1590056929334.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:19
Joined
May 7, 2009
Messages
19,169
where did you put the Autonumber field, in MaterialNo or MaterialTempFile?
you should put it on the table you are posting at post #9.
 

Gismo

Registered User.
Local time
Today, 23:19
Joined
Jun 12, 2017
Messages
1,298
where did you put the Autonumber field, in MaterialNo or MaterialTempFile?
you should put it on the table you are posting at post #9.
in the material temp file
this is a query on a different table than earlier in the discussion
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:19
Joined
Feb 19, 2013
Messages
16,553
so to be clear, your autonumber field is called LineNumber?

Also, because you are using is spaces in your table name, you must use square brackets

"[Material Temp File]"
 

Gismo

Registered User.
Local time
Today, 23:19
Joined
Jun 12, 2017
Messages
1,298
so to be clear, your autonumber field is called LineNumber?

Also, because you are using is spaces in your table name, you must use square brackets

"[Material Temp File]"
Yes, LineNumber is the autonumber.

I had square brackets around the table name, still gives me the number of lines total per line as a 9 as in post #9

#LineNo: DCount("*","[Material Temp File]","Linenumber<=" & [Linenumber])#

1590084904492.png
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 21:19
Joined
Feb 19, 2013
Messages
16,553
might be an idea to show the linenumber column in your data
 

Gismo

Registered User.
Local time
Today, 23:19
Joined
Jun 12, 2017
Messages
1,298
might be an idea to show the linenumber column in your data
I now feel like a fool
i changed the line number to auto number but there was data in the table so it never saved
I tried to do a Dcount on all zero values hence the duplication in dcount

thank you all for the assistance
i works perfect
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:19
Joined
Feb 19, 2002
Messages
42,976
Count() and dCount() count the number of items selected by the criteria. They do NOT count the number of duplicates.

You never gave us a set of data and the result you wanted so I can't tell what you were actually trying to do. In all your examples, the criteria you used was basically - a=a which doesn't do anything. You would get the same results if you used no criteria.

dCount() should never be used in a query or in a VBA code loop because each domain function runs a separate query. If you have a small number of records, it won't matter much if you end up running a hundred or two separate queries but once your recordset gets into the 10's of thousands, you will see a huge slowdown. I'm guessing that to give you what you need, you should be using Count() anyway.

I know you said you have the results you want but I think you still have a problem. Please post the entire query. Post the recordset and the results you want.
 

Users who are viewing this thread

Top Bottom