You tried to execute a query that doesn't include the specified expression as part of an aggregate function

Heres the query

Price Each USD: IIf(Left([mc476mth]![plant],2)="BR",([mm60]![price]/[Forms].[main]![brl to usd]),IIf(Left([mc476mth]![plant],2)="CA",([mm60]![price]/[Forms].[main]![cad to usd]),IIf(IsNull([mc476mth]![plant]),"N/A")))

What's wrong w/ this?  Even if I change it to a "GROUP BY" it still gives me the same error.
akh7025Asked:
Who is Participating?
 
Steve BinkConnect With a Mentor Commented:
When you create a query using a GROUP BY clause, each field listed MUST be:

a) Also listed in the GROUP BY clause, or
b) part of an aggregate function.

For example, this query will return exactly the same error:

SELECT F1, F2 FROM MyTable GROUP BY F1

To make the query work, you could do one of two changes:

a) SELECT F1, F2 FROM MyTable GROUP BY F1, F2
b) SELECT F1, Count(F2) FROM MyTable GROUP BY F1

You can find a list of acceptable aggregate functions by search Access help for "aggregate function".  Two examples are SUM and COUNT.
0
 
nico5038Commented:
Try:

[Price Each USD]: IIf(Left([mc476mth]![plant],2)="BR",([mm60]![price]/[Forms].[main]![brl to usd]),IIf(Left([mc476mth]![plant],2)="CA",([mm60]![price]/[Forms].[main]![cad to usd]),IIf(IsNull([mc476mth]![plant]),"N/A")))

The [ and ] is needed for the spaces, better never to use spaces in fieldnames...

Nic;o)
0
 
Data-ManCOOCommented:
Can you post the whole SQL Statement?

Thanks,
Mike
0
 
shanesuebsahakarnConnect With a Mentor Commented:
You could wrap the whole thing up in a function such as First. For example:

Price Each USD: First(IIf(Left([mc476mth]![plant],2)="BR",([mm60]![price]/[Forms].[main]![brl to usd]),IIf(Left([mc476mth]![plant],2)="CA",([mm60]![price]/[Forms].[main]![cad to usd]),IIf(IsNull([mc476mth]![plant]),"N/A"))))

and change the group by to "Expression". Without looking at your SQL though, I suspect one of the comments posted by others above will be closer to the mark.
0
All Courses

From novice to tech pro — start learning today.