DCount Multiple Criteria (1 Viewer)

[ijaz196]

I have a employees table which contains following fields:-
(a) Rank
(b) Name
(c) Trade
(d) Date_of_Induction
(e) Marital_Status
(f) Living_Status
(g) Inactive (Check Box)

when I enter =DCount("[Rank]","Basic_Info","[Rank] = 'HS-II'") in unbound box it counts all active & inactive employees, but when enter =DCount("[Rank]","Basic_Info","[Rank] = 'HS-I'" and “[Inactive] =’False’”) to count rank wise employees which are in service it turns to error message.


(ii) I also want to count rank wise employees who are married and in service

any one can help me please?
Thanks

 

[arnelgp]

Remove the single quote from False:

DCount("[Rank]","Basic_Info","[Rank] = 'HS-I' and [Inactive] =False”)

 

[The_Doc_Man]

Hello, ijaz, and welcome.

Just so you will know, you have posted in a "social" segment of the forum. You should post this kind of question under Access Discussion >> General.

Here is a before-and-after for your first question. You might be able to figure out from this what you need to do for your second question:

=DCount("[Rank]","Basic_Info","[Rank] = 'HS-I'" and “[Inactive] =’False’”)

=DCount("[Rank]","Basic_Info","([Rank] = 'HS-I') and ([Inactive] = False)”)

Changes:

1) That AND internal to the criteria needs to be inside the string you are passing to DCount.

2) Since Inactive is a check box, it is going to be True or False, which are NOT strings - but you had False quoted, which would have forced it to be a string. You don't quote T/F values or numbers. You quote strings with either "xxx" or 'xxx' and you quote dates with octothorpes as #1-Jan-2018#

3) The parentheses in the criteria string are technically not needed but they make it visually easier for you to see what you are doing. When you tackle your 2nd question, they will come in handy because by that time you will be dealing with multiple (>2) criteria segments.

 

[ijaz196]

Thanks for a early reply but it not working

When I inter
=DCount("[Rank]","Basic_Info","[Rank] = 'HS-I'" and “[Inactive] =’False’”)
message appears that "The expression you entered contains invalid syntax, You may have entered an operand without and operator" and
when i entered
=DCount("[Rank]","Basic_Info","([Rank] = 'HS-I') and ([Inactive] = False)”)
message appears that "The expression you entered has and invalid string, a string can be up to 2048 characters long, including opening and closing quotation marks.

 

[ijaz196]

Thanks Mr. arnelgp
when i apply
DCount("[Rank]","Basic_Info","[Rank] = 'HS-I' and [Inactive] =False”)
The message appears that "The expression you entered has and invalid string, a string can be up to 2048 characters long, including opening and closing quotation marks.

 

[plog]

Congratulations, I don't know how you did it but you have 3 types of quote marks in your examples.

"[Rank] = 'HS-I' and [Inactive] =False”)

The first ones are simple double quotes right before [Rank].
The second ones are single quotes around HS-I
The very last ones are slanted double quotes after False.

Change the very last ones to simple double quotes like you started the thing with.

 

[arnelgp]

That is strange, where did you put the expression? On the control source of textbox?

 

[ijaz196]

I put this expression in text box

 

[ijaz196]

Thanks Mr. Arnelgp and Mr. plog
This is working
Thanks again.

Now I want to counts the following fields
(a) Rank
(b) Marital_Status
(c) Inactive

Which expression is used for this counting, please help me. I will be great full to both of you. I request for help again.
Thanks

 

[pbaldy]

FYI, I moved the thread out of the site suggestions forum.

 

[isladogs]

Thanks Mr. Arnelgp and Mr. plog
This is working
Thanks again.

Now I want to counts the following fields
(a) Rank
(b) Marital_Status
(c) Inactive

Which expression is used for this counting, please help me. I will be great full to both of you. I request for help again.
Thanks

DCount again with suitable criteria

 

[arnelgp]

DCount("[Rank]","Basic_Info","[Rank] = 'HS-I' and [marital_status]='Married' and [Inactive] =False”)