We help IT Professionals succeed at work.

DCOUNT

gigifarrow
gigifarrow used Ask the Experts™
on
Say you have a table named '"tblMODSKits" and in this table the is a model number named,"ACS-A3" how do i use a dcount or count to get the totals for that model number?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2013

Commented:
msgbox DCOUNT("*","tblMODSKits", "ModelNUmber = '" & "ACS-A3" & "'")

Author

Commented:
Okay, thanks but that is not working. I thought it had to have a = sign before DCOUNT? It just says #error.
If you're looking to count the number of check marks in each row of that table, use this query:


SELECT tblMODKits.[MOD Kit], Abs([M2A2]+[M2A2 ODS]+[M2A2 ODS SA]+[M2A3]+[M3A2]+[M3A2 ODS]+[M3A3]+[M3A2 ODS SA]+[M7 BFIST]+[A3 BFIST]+[M7 BFIST SA]+[A3 BFIST w/FS3]) AS [MOD Kit Total]
FROM tblMODKits;

Flyster

Author

Commented:
Thanks for all the help but I just need to add the total of each MOD Kit installed.
I trying to do something like this

 =DCOUNT("*","tblMODKit","MOD Kit","ACS-A3") I dont know what is wrong with the code. I didnt put the table up. How do you know what table Im talking about?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<No Points wanted>

<Okay, thanks but that is not working. I thought it had to have a = sign before DCOUNT? It just says #error.>
You never stated how you were using this.

mbizups post will just display the count for you in a message box.
Just to verify if the count is correct.
So if you put her code "as posted" on a button in a form it should work.

Obviously if you need it in a query or somewhere else as a formula,you may need the equal sign.
It all depends on what you are doing.

for ex., this will work on a button:
Msgbox Dcount("*","SomeTable")
But this wont:
Msgbox =Dcount("*","SomeTable")

This will work in a formula:
SomeVariable=Dcount("*","SomeTable")
But this wont:
SomeVariable Dcount("*","SomeTable")


Also check your spelling.
Since you have spaces in your field name it is best to use square brackets around them:
Dcount("*", "YourTable", "[Your Field]=" & "'" & sometext & "'")

Author

Commented:
Thanks, I should have been more clear. I am putting the code in a text box beside the field I need the totals from.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Try it like this:

=DCOUNT("*","tblMODKit","[MOD Kit]=" & "'" & ACS-A3 & "'")

This presumes the name of the Field containing the  (ACS-A3) is named "Mod Kit"

Author

Commented:
I copied and pasted that code but it is not working.  Only thing I changed is to add an "S" to "tblMODKit". Im getting this error ?name.

The table name was just off.
Are you looking for a total of 2 for ACS-A3 (M2A3 & M3A3)? DCount will not work, as it counts the number of rows with the information you're looking for. You want the number of columns in your table that has a check mark under model. The sql I provided above will do that for you. Just copy it, open a new query in design view, select SQL and paste the code there.