Solved

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

Posted on 2004-10-29
247 Views
Last Modified: 2012-06-22
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.
0
Question by:akh7025
    4 Comments
     
    LVL 54

    Expert Comment

    by:nico5038
    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
     
    LVL 18

    Expert Comment

    by:Data-Man
    Can you post the whole SQL Statement?

    Thanks,
    Mike
    0
     
    LVL 50

    Accepted Solution

    by:
    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
     
    LVL 41

    Assisted Solution

    by:shanesuebsahakarn
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    857 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now