conditional reporting

[wal50]

Each record in the table lists multiple expense categories for each employee.
Records are totals for each month. A query selects the records that have

$0 in one specific expense category within the last 6 months.

The goal is to produce a report by employee of only those employees who have
three or more instances (months) with expense activity in the given category.

I can count the instances per employee but there I'm stuck. I am lost as
to how to not print (filter out) the ones where the count if <3.

Any ideas are appreciated.
Thanks in advance
WAL50

 

[Ken Sheridan]

You could either base the report on a query which uses a subquery to restrict
the rows returned, e.g. using your existing query as the basis for this:

SELECT *
FROM YourQuery AS Q1
WHERE
(SELECT COUNT(*)
FROM YourQuery As Q2
WHERE Q2.EmployeeID = Q1.EmployeeID
AND Q2.ExpenseCategory = Q1.ExpenseCategory)

=3;

Or, using your existing query as the basis for the report, in the Format
event procedure of the report's detail section cancel the row if there are
less than 3 rows for the current employee/expense category in the query, e.g.

Dim intExpensesCount as Integer
Dim strCriteria As String

strCriteria = _
"EmployeeID = " & Me.EmployeeID & _
" And ExpenseCateory = " & Me.ExpenseCategory

intExpensesCount = DCount("*","YourQuery", strCriteria)

Cancel = (intExpensesCount < 3)

The above assumes that the EmployeeID and ExpenseCategory columns are of
number data type. For a text data type you'd need to wrap the values in
quotes, e.g. if EmployeeID is a number data type, but ExpenseCategory a text
data type:

strCriteria = _
"EmployeeID = " & Me.EmployeeID & _
" And ExpenseCateory = """ & Me.ExpenseCategory & """"

Ken Sheridan
Stafford, England

 

[wal50]

Thanks Ken.
Now I have:
Dim intExpensesCount As Integer
Dim strCriteria As String

strCriteria = _
" Additional MOU = " & QryMinutesBasis.Additional MOU & _
" And Service ID 1 = """ & QryMinutesBasis.Service ID 1"""

intExpensesCount = DCount("*", "QryMinutesBasis", strCriteria)

Cancel = (intExpensesCount < 3)

where Additional MOU is an integer and Service ID is Text. I get Expected :
End of Statement. Do I have the "" wrong?

Thanks again.

WAL50

 

[Ken Sheridan]

Three thins:

1. As your column names include spaces they need to be wrapped in square
brackets when referencing them.

2. You need to reference the report's controls, not the query's columns.
An additional factor with reports is that, unlike code in a form's module you
can't reference a column in a report's underlying recordset directly, only a
control, so if the [Additional MOU] and [Service ID 1] controls do not have
controls in the report's detail section bound to them you'll need to add text
box controls bound to theses columns and set their Visible property to False
(No) to hide them. Note that the references in the code are to the controls,
not the columns they are bound to, so if the control name is not the same as
the column's then make sure the references in the code are to the control
names. You can reference the report by means of 'Me'.

3. When building the expression for the strCriteria variable you need to
concatenate a literal quotes character onto the end. A literal quotes
character within a string expression is denoted by a pair of contiguous
quotes characters.

So, putting all that together gives:

strCriteria = _
" [Additional MOU] = " & Me.[Additional MOU] & _
" And [Service ID 1] = """ & Me.[Service ID 1] & """"

Ken Sheridan
Stafford, England

 

[wal50]

These are great sites. Thanks for the education

Three thins:

1. As your column names include spaces they need to be wrapped in square
brackets when referencing them.

2. You need to reference the report's controls, not the query's columns.
An additional factor with reports is that, unlike code in a form's module you
can't reference a column in a report's underlying recordset directly, only a
control, so if the [Additional MOU] and [Service ID 1] controls do not have
controls in the report's detail section bound to them you'll need to add text
box controls bound to theses columns and set their Visible property to False
(No) to hide them. Note that the references in the code are to the controls,
not the columns they are bound to, so if the control name is not the same as
the column's then make sure the references in the code are to the control
names. You can reference the report by means of 'Me'.

3. When building the expression for the strCriteria variable you need to
concatenate a literal quotes character onto the end. A literal quotes
character within a string expression is denoted by a pair of contiguous
quotes characters.

So, putting all that together gives:

strCriteria = _
" [Additional MOU] = " & Me.[Additional MOU] & _
" And [Service ID 1] = """ & Me.[Service ID 1] & """"

Ken Sheridan
Stafford, England

Thanks Ken.
Now I have:
Dim intExpensesCount As Integer
Dim strCriteria As String

strCriteria = _
" Additional MOU = " & QryMinutesBasis.Additional MOU & _
" And Service ID 1 = """ & QryMinutesBasis.Service ID 1"""

intExpensesCount = DCount("*", "QryMinutesBasis", strCriteria)

Cancel = (intExpensesCount < 3)

where Additional MOU is an integer and Service ID is Text. I get Expected :
End of Statement. Do I have the "" wrong?

Thanks again.

WAL50