getting subquery error message

[Lhuffst]

I have a report that does multiple types of counts
1. Count the number of violations per industry. This works fine.
2. count the industries that meet a certain criteria ...get a syntax error on this
3. count the industries that meet the criteria in item 2 AND are active .... haven't done yet

Can someone look at the dcount I used for item 2 and tell me what I did wrong?
Also how would I modify item 2 to add in the 3rd conditions of being active?

=DCount("PermitNo","t_PermitAccounts","PermitNo Not In (""13819"",""13820"",""13829"") & " AND t_PermitOps.ClassCode IN ('FOG', 'BMP') "  WHERE t_PermitAccounts.PermitNo = t_PermitOps.PermitNO)")

thanks in advance Lhuffst

 

[MajP]

I did not look at the whole thing but at a minimum you have the word "where" in the middle of the constraint.

 

[MajP]

Why double quotes here: ""13819"",""13820"",""13829""? If numbers no quotes, if text single quotes.

 

[Lhuffst]

I replaced the double quotes with a single quote and now it says #error

replaced code

=DCount("PermitNo","t_PermitAccounts","PermitNo Not In ('13819','13820','13829')  AND t_PermitOps.ClassCode IN ('FOG', 'BMP')   WHERE t_PermitAccounts.PermitNo = t_PermitOps.PermitNO)")

I also did this in a query which returned the right count. I made a subreport to display the count but when I add it to the report, it just displays as blank.
I didn't put parent/child links because I need it to count all of the industries not just the ones that had violations. Any suggestions?

 

[MajP]

So if you can make a correct query you can use a dcount on that
=dcount("*","yourNewQuery")
The clause "where" in your above dcount is incorrect. I assume it should be "AND" or "OR".