Dcount with multiple criteria 1

[CharlieT302]

Hi Everyone,

I am using a Dcount to count up records that match three criteria:

Per_ID = Number Field
Org_ID = Number Field
Conf = Yes/No Field

The following does not work:
If DCount("Org_ID", "Tbl_Deployed_Facilities", Per_ID = Me![Per_ID] And Org_ID = Me![Org_ID] & " And Conf = -1") > 0 then
Msgbox...

Or this one:

DCount("Org_ID", "Tbl_Deployed_Facilities", Per_ID = Me![Per_ID] And Org_ID = Me![Org_ID] And Conflicted = -1) then

(Note: When attaching the formula to a message box for a test, it produces a zero answer.)

A simple test version
Msgbox DCount("Org_ID", "Tbl_Deployed_Facilities", Org_ID = Me.Org_ID)

This formula summarizes "all" the Org ID's rather than those matching the one active on the form. Clearly my syntax is incorrect.

Greatly appreciate the help.

 

[PWise]

how about

DCount("Org_ID", "Tbl_Deployed_Facilities", "Per_ID = " & Me![Per_ID] & " And Org_ID =" &  Me![Org_ID] & " And Conf = -1")

 

[CharlieT302]

That works great. Thank you very much. If you have the time, could you explain the placement of the quote marks? This always gives me trouble.

It appears as though you are encasing the fields from the domain in quotes but not the value that is being searched (me!Org_ID). Is this the syntax for numeric fields only?

 

[hmlhml]

The third parameter of Dcount (or Dlookup) is a string that must look like the WHERE clause of an sql query, without the "where". Numerical values in sql queries don't need quotes. If you're ever in any doubt about what quotes you need, you can build an appropriate query in the design view, switch to sql view, and cut-and-paste.
In PWise's case, all the quotes are merely to build up the three strings to pass to Dcount as its parameters; none of the things you are matching is actually in quotes so far as Dcount is concerned. Dcount is actually receiving three strings containing the thing to count, the table in which to look, and the conditions that entries must meet in order to be counted, and none of these strings contains any quotes of any sort. Hope that helps a bit in explanation?

 

[CharlieT302]

It looks like I spoke to soon. At first, the code appeared to be working but now I am catching an error. I didn't catch it before because I had suppressed any system error messages in my Exit_sub: line. Also, the code actually is safegaurding my data correctly.

However, when I allow system error messages, I receive the following:

3075 Syntax Error (missing operator) in query expression 'Per_ID = And Org_ID = 2 and Deployed = -1'

Note: the code is attached to a combo box on a subform. This error occurs only when the main form is on a blank record. I could modify the code to trap the error on a null primary record, but I am concerned about receiving a "syntax" error.

Can you see a flaw in the code I could correct?

 

[PWise]

this depends on what you want to do

there are two thing either

DCount("Org_ID", "Tbl_Deployed_Facilities", "Per_ID = " & nz(Me![Per_ID],0) & " And Org_ID =" &  nz(Me![Org_ID],0) & " And Conf = -1")

or trap the error

if nz(me.parent.xxx,0)=o then exit sub