Count Query (1 Viewer)

karmaimages

Registered User.
Local time
Today, 07:31
Joined
Nov 19, 2009
Messages
15
Hi,

I have a form where I want to show some statistics from the database like count how many bits of outstanding work there are for a certain department.

I have a table called work_items - With various fields one being "Department" - I want on the form to display in a text box the number of times say "Sales" is selected as the department from the combo box.

So table is - work_items
Field - Department

Criteria to count - "Sales"

I can't seem to get a dcount to work, any help appreciated.
 

cyanidem

Nieóhfytny jaszczomp
Local time
Today, 14:31
Joined
Nov 30, 2015
Messages
106
DCount("[Department]", "work_items", "[Department] = 'Sales'")
 

karmaimages

Registered User.
Local time
Today, 07:31
Joined
Nov 19, 2009
Messages
15
DCount("[Department]", "work_items", "[Department] = 'Sales'")


That works perfectly thank you.

If I wanted to add a further criteria - Where field is Completed "yes" or "no"

Would this be
Code:
DCount("[Department]", "work_items", "[Department] = 'Sales' & [Work Complete]='No'")

I did attempt the above but it shows as #Error?
 

cyanidem

Nieóhfytny jaszczomp
Local time
Today, 14:31
Joined
Nov 30, 2015
Messages
106
DCount("[Department]", "work_items", "[Department] = 'Sales' And [Work Complete]='No'")
 

cyanidem

Nieóhfytny jaszczomp
Local time
Today, 14:31
Joined
Nov 30, 2015
Messages
106
What type is [Work Complete] field? Is it text or maybe Yes/No field? If Yes/No then try this:
DCount("[Department]", "work_items", "[Department] = 'Sales' And [Work Complete]=0")
 

karmaimages

Registered User.
Local time
Today, 07:31
Joined
Nov 19, 2009
Messages
15
Actually that worked, not sure what I did to make it work, originally it didn't but entered the expression again and it seemed to work :)

Thanks for your help
 
Last edited:

cyanidem

Nieóhfytny jaszczomp
Local time
Today, 14:31
Joined
Nov 30, 2015
Messages
106
I guess you're trying to mix different things. You are showing me properties of some combo box on form. DCount requires criteria referencing fields in table. Is there a field named [Work Complete] in work_items table? If so - what is its data type?
 

karmaimages

Registered User.
Local time
Today, 07:31
Joined
Nov 19, 2009
Messages
15
I guess you're trying to mix different things. You are showing me properties of some combo box on form. DCount requires criteria referencing fields in table. Is there a field named [Work Complete] in work_items table? If so - what is its data type?

Sorry I edited the post before you replied, it seems to be working now which is great.

Second Query is it easy to display the total number of records in the work_items table - like a count of the "Surname" field as long as it counted duplicate surnames and or a total count of the Work Complete field if easier?
 

virus

New member
Local time
Today, 07:31
Joined
Feb 12, 2015
Messages
8
Hi there...


I have also a problem of DCOUNT, this is my query expression:

DCount("[DUEDATE]", "LNINST", "[DUEDATE] < 42369")

Note: DUEDATE field was converted into numeric character ... where 42369 is equivalent to 12/31/2015..

In DUEDATE field, its include the next maturing date where above 42369 or 12/31/2015. The result of my formula was 2 instead of 1.

Anybody can suggest what expression should i write?
 

Users who are viewing this thread

Top Bottom