Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Expression in query (A2k3; SP2)

    Hi All,
    The following is the SQL view from a query:
    <pre>SELECT DISTINCT tblWOMods.PNMod,
    tblWOMods.Status,
    Sum(Nz([CompleteQuantity],0)) AS TotalCompQuant,
    Count(tblDefectLog.DefectPKID) AS DefectCount,
    Format([DefectCount]/[TotalCompQuant],"Percent") AS PerCentyld
    FROM tblWOMods INNER JOIN tblDefectLog ON tblWOMods.WOModsPKID = tblDefectLog.WOModsPKID
    GROUP BY tblWOMods.PNMod, tblWOMods.Status;
    </pre>


    As you can see, if I have "NO" CompleteQuantity entered I'll end up with a "divide-by-zero", but it does work with a TotalCompQuant > 0.
    So I tried:
    <pre>PerCentYld: IIf([CompleteQuantity]<>0,Format([DefectCount]/[TotalCompQuant],"Percent"),0)
    </pre>

    I then get the error:
    "You tried to execute a query that does not include the specific expression
    PerCentYld: IIf(Not [CompleteQuantity]=0,Format([DefectCount]/[TotalCompQuant],"Percent"),0)
    as part of an aggregate function.
    In the design view of the query the Total type is set to Expression.
    So, what am I not doing (or doing what I shouldn't) and how do I set this up correctly?
    Thank you.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression in query (A2k3; SP2)

    I think it's because you are comparing a non summarized field.

    Change your.

    PerCentYld: IIf(Not [CompleteQuantity]=0,Format([DefectCount]/[TotalCompQuant],"Percent"),0)
    to

    PerCentYld:: IIf(Not Sum(Nz([CompleteQuantity],0))=0 ,Format([DefectCount]/[TotalCompQuant],"Percent"),0)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression in query (A2k3; SP2)

    Does it work if you use

    PerCentYld: IIf([TotalCompQuant]<>0,Format([DefectCount]/[TotalCompQuant],"Percent"),0)

  4. #4
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression in query (A2k3; SP2)

    I see what you mean.
    Thank you.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression in query (A2k3; SP2)

    Thank you. Yes it does.
    Is either of those 2 "working" ways better than the other? e.g. quicker, less confusing, etc?
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression in query (A2k3; SP2)

    I don't think it'll make any difference in performance - Jet SQL probably generates the same execution path after optimization.
    So it's a matter of personal preference - pick the one you like best <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •