Filter without grouping

[Brian]

I'm struggling a little with finding a good method to pass a filter to a
report where the field to which the filter applies is not the basis of a
group in the report. That is, I want to FILTER a report by some combination
of customer, item, and date (i.e. user-selectable--any or all of these may be
used as filters concurrently); however, I want to GROUP the report by
customer only and show just the item count within the item & date range.

If I make the report's RecordSource a query that includes summary functions
(i.e. Sum of ItemCount) and send through the Where clause in the OpenReport
method, it chokes on the fact that one or more elements in the Where clause
does not appear in the RecordSource except as another Where (e.g. Where Not
IsNull(ItemID)).

I cannot use Me.Filter for the same reason: if the field appears in a
summarized query, it must be grouped by that field; I cannot just make the
field an Expression.

 

[Duane Hookom]

Live with it. You won't be able to either use a filter or use the Where
condition of the OpenReport method if your filtering field is not in the
report's record source.

You can "hardcode" the criteria in the record source like:
SELECT FieldA, Sum(FieldB) as SumB
FROM tblA
WHERE FieldC Between Forms!frmCrit!txtStart and Forms!frmCrit!txtEnd
GROUP BY FieldA;

You could also store the criteria as values in fields of a single record
table or modify the SQL property of a saved query.

 

[Brian]

Thanks, Duane.

Part of the problem is that the criteria are optional: that is, I have
allowed the user to select or not select any of five or six criteria, so any
of them could be null or populated.

For some reason, I did not get the e-mail indicating your post, and in the
meantime, I just built the RecordSource as a string in the form from which
the report is opened, adding only those criteria to the WHERE clause that are
actually selected by the user, and passed it to the report as OpenArgs. Then
in the Open event of the report, I set Me.RecordSource = Me.OpenArgs.

I was just being lazy when I posted. I sometimes get snarled up in the
"quotes within quotes" when concatenating long string-criteria-laden SQL
statements into strings to pass as arguments. I just wrote one version of the
parameterized query, copied the SQL to the procedure on the form that now
creates the RecordSource, split it up into pieces to build the appropriate
elements, and then got all the quotes in the right place so that the
string-related criteria quotes appeared correctly within the string.

Works flawlessly.

Thanks again.

"

 

[Duane Hookom]

Glad to hear you got this working. Your solution is similar to my suggestion
to change the SQL property of a saved query.

 

[Brian]

So, do you know a way to leave a parameter in a query and simply ignore it
when it is null, rather than filter to IsNull? That would have saved me a lot
of time, but the only way I know to do that in a query is to have two
separate criteria lines: one for when the parameter is null and one when it
is not.

 

[Duane Hookom]

You can add an expression like:
WHERE DeptID = Forms!frmCriteria!cboDeptID Or Forms!frmCriteria!cboDeptID Is
Null

Another method is to use if the field is always valued (no nulls):
WHERE DeptID = Nz(Forms!frmCriteria!cboDeptID, [DeptID])

Another method is to use if the field might be null:
WHERE Nz(DeptID,"") = Nz(Forms!frmCriteria!cboDeptID, Nz([DeptID],""))

 

[Brian]

These would have spared me some time here. I tried something similar to the
first one, but relying on the Access query builder to build the WHERE clause
when there are several possible criteria is much more complex than manually
inserting the WHERE clause into the SQL statement.

Thanks again.

You can add an expression like:
WHERE DeptID = Forms!frmCriteria!cboDeptID Or Forms!frmCriteria!cboDeptID Is
Null

Another method is to use if the field is always valued (no nulls):
WHERE DeptID = Nz(Forms!frmCriteria!cboDeptID, [DeptID])

Another method is to use if the field might be null:
WHERE Nz(DeptID,"") = Nz(Forms!frmCriteria!cboDeptID, Nz([DeptID],""))

--
Duane Hookom
Microsoft Access MVP

So, do you know a way to leave a parameter in a query and simply ignore it
when it is null, rather than filter to IsNull? That would have saved me a lot
of time, but the only way I know to do that in a query is to have two
separate criteria lines: one for when the parameter is null and one when it
is not.