Expression in query (A2k3; SP2)

Topic

New Reply

Hi All,
The following is the SQL view from a query:

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;

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:

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

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.

Reply | Quote

Replies

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)

Reply | Quote

I see what you mean.
Thank you.

Reply | Quote

Does it work if you use

PerCentYld: IIf([TotalCompQuant]0,Format([DefectCount]/[TotalCompQuant],”Percent”),0)

Reply | Quote

Thank you. Yes it does.
Is either of those 2 “working” ways better than the other? e.g. quicker, less confusing, etc?

Reply | Quote

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

Reply | Quote