If statement

B

Barry

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 :)
 
A

Arvin Meyer [MVP]

Use the Immediate If function:

IIf([FieldName]<expression>, TruePart,FalsePart)

and the IsNull() function:

IIf(IsNull([MyField), Sum([MyOtherField], Max([MyOtherField])
 
A

Arvin Meyer [MVP]

Sorry, I've been on vacation with spotty Internet.

You need to give the column an alias like:

Expr1: IIf(IsNull([MyField), Sum([MyOtherField], Max([MyOtherField])

--
Arvin Meyer, MCP, MVP
Microsoft Access
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Do not use an aggregate query, since the Sum and Max functions are part of
the expression.
Barry said:
Arvin, I tried this but it returned the infamous "You tried to execute a
query that does not include the specified expression...as part of an
aggregate function."

Jamie, is it possible to use one of your expressions that includes both
the
MAX and SUM function in one field?

Thanks
--
Barry


Arvin Meyer said:
Use the Immediate If function:

IIf([FieldName]<expression>, TruePart,FalsePart)

and the IsNull() function:

IIf(IsNull([MyField), Sum([MyOtherField], Max([MyOtherField])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Barry said:
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 :)
Click to expand...
Click to expand...
Click to expand...
 
Top