Counting specific records of a parameter query in a report

[zdjbel]

Hi,

I have a report whose record source is a query qProjectStatus, which takes a parameter [Project] from a ListBox on a form. The report's header has a text box where I want to show the number of records whose status [StCode] matches the numbers 1 through 6 only (status choices available are numbers in number, not text format, from 0 - 15).

I tried:

=DCount("[StCode]","[qProjectStatus]","[StCode] = 1 or [StCode] = 2 or [StCode] = 3 or [StCode] = 4 or [StCode] = 5 or [StCode] = 6")

. The result is a sum of the number of records matching the given criteria from the whole underlying table, and not just from the query result which is what I need.



Thanks very much in advance.

[June7]

Need to include the Project parameter in the DCount expression.

=DCount("[StCode]","[qProjectStatus]","[StCode] Between 1 And 6 And [Project]=" & [Project])

[zdjbel]

Hi June 7,

Thanks for the quick response, however, it didn't get me the result I need, just "#Error". I think it has to do with when the DCount function is executed vs. when the query takes the parameter from the list box, but I'm just speculating here. I'm using Allen Browne's "Open the report filtered to the items selected in the list box" code below. to filter the report, and I want to count the records using the criteria from the DCount only after the report has been filtered. Am I over complicating? Is there a simpler way to do this? Thanks much.

On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   http://allenbrowne.com
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of FORM (report) to open.
   
    strDelim = """"            'Delimiter appropriate to field type. See note 1.
    strDoc = "fProject"
 
    'Loop through the ItemsSelected in the list box.
    With Me.lstProject
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
   
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[Project] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "Categories: " & Left$(strDescrip, lngLen)
        End If
    End If
   
        DoCmd.OpenForm strDoc, acNormal, WhereCondition:=strWhere
          
Exit_Handler:
    Exit Sub
 
Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Form cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdOpenfProject_Click"
    End If
    Resume Exit_Handler
   
End Sub

[June7]

The DCount has nothing to do with records displayed on report. It is pulling data directly from referenced table or query according to its own criteria.

Is [Project] a field on the report?

[zdjbel]

Yes, [Project] is a field on the report, the query is attached.

[June7]

Textbox on report bound to Project field? Is it also in the group header? If it is a text field, need apostrophe delimiters: [Project]='" & [Project] & "'")

[zdjbel]

A friend figured out how to get the result I need. The solution is 2 step:

1. Create an invisible text box in the report detail, name it txtOrdered and set its control source to “=IIf([StCode]=1,1,IIf([StCode]=2,1,IIf([StCode]=3,1,IIf([StCode]=4,1,IIf([StCode]=5,1,IIf([StCode]=6,1,IIf([StCode]=9,1)))))))”

2. Create a text box in the report footer and set its control source to txtOrdered. It will work only on the report footer and not in the page footer or the header.

Thanks June 7 for taking the time to help me, I appreciate it very much. Respectfully,

zdjbel

[June7]

Explore the Switch and Choose functions, often neater than nested IIf expression.

Don't understand that expression, every condition returns 1. Also, missing the alternate value in the last IIf.

=IIf([StCode]<=6 Or [StCode]=9,1,something else)