DCount (1 Viewer)

Gismo

Registered User.
Local time
Tomorrow, 00:18
Joined
Jun 12, 2017
Messages
418
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, 22:18
Joined
Feb 19, 2013
Messages
12,091
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
Tomorrow, 00:18
Joined
Jun 12, 2017
Messages
418
when I use a text field, i get a data type mismatch error
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:18
Joined
Feb 19, 2013
Messages
12,091
for text your criteria should be

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

Gismo

Registered User.
Local time
Tomorrow, 00:18
Joined
Jun 12, 2017
Messages
418
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, 22:18
Joined
Feb 19, 2013
Messages
12,091
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
Tomorrow, 00:18
Joined
Jun 12, 2017
Messages
418
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, 22:18
Joined
Feb 19, 2013
Messages
12,091
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
Tomorrow, 00:18
Joined
Jun 12, 2017
Messages
418
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

error reading drive A:
Local time
Tomorrow, 05:18
Joined
May 7, 2009
Messages
9,273
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
Tomorrow, 00:18
Joined
Jun 12, 2017
Messages
418
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, 22:18
Joined
Feb 19, 2013
Messages
12,091
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
Tomorrow, 00:18
Joined
Jun 12, 2017
Messages
418
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:

Gismo

Registered User.
Local time
Tomorrow, 00:18
Joined
Jun 12, 2017
Messages
418
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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom