I believe that the problem may be that access may try to evaluate both the
true and the false options before deciding which one is wanted.

"Barry" wrote in message
...
Yes, thanks to Allen Browne, his suggestion, IIf([CreditDate] Is Not Null,
[CreditAmount], 0)

You can then Sum or choose the Max of this value in the Total row,

does work to do one or the other (either sum or max). But, what I am
trying
to do is to do a combination of the two (both sum or max) in one
expression,
based on whether or not the other field contains a null value or not.

--
Barry Guidry


"Duane Hookom" wrote:

If I understand correctly, Allen Browne's suggestion should work. It's
difficult to determine since you included a data record 4... ...2222
that
seems to be ignored and the "4" is the same as the result you are
expecting
from the Result column.
--
Duane Hookom
Microsoft Access MVP


"Barry" wrote:

Yes, Duane you are right...the null field is based on each record, but
I
would like the statement to sum the values [Result] if there are any
rows of
null values in the other field [Qual].

Example:

'Result' 'Qual' 'Lab_id'
1 1111
2 J 1111
3 1111
4 2222
----------------------------
4 1111 ('Result' being equal to four because
the
'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id'
as
"1111"). The [Lab_id] is the field I want the query to sort the data,
and
calculation, by. Is there any way to do this without specifying
specific
values in the statement, but rather all values that are the same?

--
Barry Guidry


"Duane Hookom" wrote:

I think you need to provide some context and sample data as well as
desired
results. Sum and Max work against a group of records while "another
field is
null" seems to work against just a single record.

--
Duane Hookom
Microsoft Access MVP


"Barry" wrote:

I would like to make a query on a table on one field to 'sum' or
'max' the
field based on 'if' another field is null or not. Any idea on how
I would
go about this? Please be generous as I am not an Access expert :-)
--
Barry