Dcount problem

[AndyCompanyZ]

I have a DCount on a textbox on a form that is
supposed to pull up the number of delegates scheduled or invited (status 1 or
2) form a table tbleventdelegate. I have the following code:

=DCount("[Status]","tblEventDelegate","[EventID]=" & [Forms]!
[frmMainNavigation]![NavigationSubform]![EventID] & "AND [Status]=1 and 2")

this only gives me 1 record when there are 2 records one with a
status of 1 and one with 2. I tried 1 or 2 but that brought up all the
records and I tried 1,2 but that gave me a #type error in the box. I even
tried 1 +2 but that only gave me 1 result on one record of the tbale
presumably because there is a record with a status of 3 for it. I'm sure this is an easy solution but I can't find the answer I have been looking all day. Thanks in advance to anyone

 

[pbaldy]

Instead of

AND [Status]=1 and 2

try

AND ([Status]=1 OR [Status] = 2)

You have to repeat the field and test for each value, and you want parentheses around the OR statement for the logic to work correctly. You could also use an IN() clause, but I wanted to show you where you went wrong.

 

[AndyCompanyZ]

Thanks for that but it still gives me a problem. I tried:

=DCount("[Status]","tblEventDelegate","[EventID]=" & [Forms]![frmMainNavigation]![NavigationSubform]![EventID] & " AND ([Status]=1 or [Status] =2")

But that gives an #error and I tried:
=DCount("[Status]","tblEventDelegate","[EventID]=" & [Forms]![frmMainNavigation]![NavigationSubform]![EventID] & " AND ([Status]=1 or [Status] =2)
But that gives me an invalid string error. Note the apostrophe difference. What is the In() clause you mention

 

[pbaldy]

You didn't close off the parentheses. Try

=DCount("[Status]","tblEventDelegate","[EventID]=" & [Forms]![frmMainNavigation]![NavigationSubform]![EventID] & " AND ([Status]=1 or [Status] =2)")

The IN() clause would look like

AND Status IN(1,2)

 

[AndyCompanyZ]

Thank you so much I knew I was near but I didn't realise about the separate parentheses around the second part. Dlookup syntax is never quite straightforward it seems.

 

[pbaldy]

No problem, glad we got it sorted out. When you mix AND & OR, you need to clarify the logic. Access may not treat

1 And 2 Or 3

the way you intend, which could be:

(1 And 2) Or 3

or

1 And (2 Or 3)

etc, so you make sure by adding the parentheses.