DCOUNT not giving expected result in subreport

[ajarrell]

Can someone tell me why this DCOUNT returns the number of records in the query without regard to the criteria? Thank you in advance.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Increment iLine on each detail format
Static iLine As Integer

iTotal = DCount("[ContactID]", "qryCompanyContacts", Me.[CompID] = 171)
'iTotal = DCount("[ContactID]", "qryCompanyContacts", Me.[CompID] = Reports![Dues Invoice]![CompID])

iLine = iLine + 1
If iLine < iTotal Then
'do nothing, print as usual
ElseIf iLine = iTotal Then
'if there are more lines to print, set the NextRecord property to false, preventing the report from exiting prematurely
If iLine < iLines Then Me.NextRecord = False
Else
'set font to white for padded row controls
'MsgBox ("Blank Line")
Me!Text9.ForeColor = vbWhite
Me!CompID.ForeColor = vbWhite
Me!Email.ForeColor = vbWhite
'prevent report from advancing past last row until all of blank lines have printed
If iLine < iLines Then
Me.NextRecord = False
ElseIf iLine > iLines Then
iLine = 0
Me!Text9.ForeColor = 4210752
Me!CompID.ForeColor = 4210752
Me!Email.ForeColor = 4210752
End If
End If

End Sub

 

[Ranman256]

you shouldnt need a Dcount.
either the query can give the count,
or a =Count(field) in the subform can count.

your WHERE is off, you need quotes there. But dont use Dcount in reports.
but you can put a dcount in a textbox too.

 

[plog]

Your criteria isn't being applied to any field in the the query:

Me.[CompID] = 171


Me.[CompID] refers to a field on your form/report

171 is a number

The query does nothing with that piece of information and returns its total results.

 

[ajarrell]

Okay, I returned to my original criteria using [CompID] with the added quote marks and it works. Many thanks to all of you!

 

[Mark_]

While you have an answer to your current question, the advice given is to help you avoid issues in the future.

Were it me, I would make a subquery that returns your COUNT per CompID. I would then join the subquery to your reports query to add the "Count" you are looking for. This mean that going forward your query will run faster, thus allowing your report to run faster.

When you have a report that uses DCount or DSUm, often you will find yourself doing this multiple times per record when you are trying to return totals of different types. Multiple calls to domain functions are quick to add to a form or report, but will cause a performance hit. Getting in to the habit of having queries do the work, and learning to join them, will greatly enhance what you will be able to do in the future.

 

[ajarrell]

Thanks for the tip!