none
cant run access query Through C# RRS feed

  • Question

  • hi

    i have this query in access that run's excellent ! ,I called her MyQUERY

    SELECT BK_LEVEL9.Aitur AS Aitur, Sum(BK_LEVEL9.M_NewQty) AS M_NewQty, Sum(BK_LEVEL9.ErrorCount) AS ErrorCount, (IIf([M_NewQty]<>0,Round(([ErrorCount]*100)/[M_NewQty],2),0)) AS ErrorProcc
    FROM BK_LEVEL9
    GROUP BY BK_LEVEL9.Aitur;

    in my C# program i try to run her like this:

    SQL = "SELECT Aitur,M_NewQty,ErrorCount,ErrorProcc from MyQUERY";
    dsWorkKabat = new DataSet();
    adp = new OleDbDataAdapter(SQL, Conn);
    adp.Fill(dsWorkKabat, "MyQUERY");
    adp.Dispose();

    and i got this error:

    You tried to execute a query that does not include the specified expression 'IIf(Not [M_NewQty]=0,Round([ErrorCount]*100/[M_NewQty],2),0)' as part of an aggregate function.

    What could be the problem ?

    thanks

    Wednesday, July 15, 2015 7:21 AM

Answers

  • You might try adding it to GROUP BY    - or - make it an aggregate function ---   First(IIf([M_NewQty]<>0,Round(([ErrorCount]*100)/[M_NewQty],2),0)) AS ErrorProcc

    Build a little, test a little

    • Marked as answer by L.HlModerator Wednesday, July 29, 2015 9:24 AM
    Wednesday, July 15, 2015 4:57 PM
  • Hi

    >>'IIf(Not [M_NewQty]=0,Round([ErrorCount]*100/[M_NewQty],2),0)' as part of an aggregate function.

    You have used Sum(BK_LEVEL9.M_NewQty) being [M_NewQty], but in the query, there is no group by for the field, which is violating the aggregate function in SQL query. you may add "group by [BK_LEVEL9.M_NewQty]" for the query.

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Wednesday, July 29, 2015 9:24 AM
    Friday, July 17, 2015 8:04 AM
    Moderator

All replies

  • Some expressions can be used in Access, but are not part of the Jet database engine. Iif is such a construct. It is from VBA. This could be evaluated in Access, but not when you're accessing it from a third-party application.

    Thus it depends on your connection string. Use the ACE provider instead of Jet:

    Provider=Microsoft.ACE.OLEDB.12.0

    Wednesday, July 15, 2015 7:43 AM
  • I don't think it's a problem with IIf() not being recognized; I think that would normally work fine.  I think the problem is that your expression:

         (IIf([M_NewQty]<>0,Round(([ErrorCount]*100)/[M_NewQty],2),0)) AS ErrorProcc

    ... isn't an aggregate expression.  That is, it isn't itself summed, minned, maxed, or otherwise aggregated, and the field references within it aren't aggregated either.  Maybe it should be:

        (IIf(Sum(BK_LEVEL9.M_NewQty)<>0,Round((Sum(BK_LEVEL9.ErrorCount)*100)/Sum(BK_LEVEL9.M_NewQty),2),0)) AS ErrorProcc

    but I'm not sure of that.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, July 15, 2015 3:40 PM
  • You might try adding it to GROUP BY    - or - make it an aggregate function ---   First(IIf([M_NewQty]<>0,Round(([ErrorCount]*100)/[M_NewQty],2),0)) AS ErrorProcc

    Build a little, test a little

    • Marked as answer by L.HlModerator Wednesday, July 29, 2015 9:24 AM
    Wednesday, July 15, 2015 4:57 PM
  • Hi

    >>'IIf(Not [M_NewQty]=0,Round([ErrorCount]*100/[M_NewQty],2),0)' as part of an aggregate function.

    You have used Sum(BK_LEVEL9.M_NewQty) being [M_NewQty], but in the query, there is no group by for the field, which is violating the aggregate function in SQL query. you may add "group by [BK_LEVEL9.M_NewQty]" for the query.

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Wednesday, July 29, 2015 9:24 AM
    Friday, July 17, 2015 8:04 AM
    Moderator
  • Some expressions can be used in Access, but are not part of the Jet database engine. Iif is such a construct. It is from VBA. This could be evaluated in Access, but not when you're accessing it from a third-party application.


    Stefan, the IIf() function that is used in queries is actually part of Jet/ACE.  It is different from the VBA IIf() function.  Demonstrably, the VBA IIf() function evaluates all arguments, while the Jet IIf() function doesn't evaluate the third  ("falsepart") argument if the first argument is True.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, July 17, 2015 1:00 PM