Adding Criteria blows up query?

[sportsguy]

I have a query which runs perfectly, and has an average price calculation, total / units, where units are always greater than 0.

As soon as I enter the criteria average price is greater than 12,000 and less than 200,000, the query gives me a divide by zero error. .

Access 2007 running on Vistax64. . .

I have always been a bit suspect with this implementation, because of one issue with query design that doesn't exist with my work laptop's Access 2007 on XP. . .

anyone else experience this issue??

thanks in advance

sportsguy

 

[DCrake]

What does you SQL stement query look like?

 

[sportsguy]

SELECT BOOKINGS.strProject, PROJECTMASTER.prjDescription, BOOKINGS_NEW.P4100U, PROJECTMASTER.[T$CCAM], BOOKINGS.FacePrice, [FacePrice]/[P4100U] AS AVGFP, BOOKINGS.Est_Material, BOOKINGS.Est_Margin, [Est_Margin]/[FacePrice] AS EMP, PROJECTMASTER.INSTALL, PROJECTMASTER.[T$ITBP], PROJECTMASTER.[T$CUSTOMER]
FROM (BOOKINGS_NEW INNER JOIN BOOKINGS ON (BOOKINGS_NEW.BookPeriod = BOOKINGS.BookPeriod) AND (BOOKINGS_NEW.System = BOOKINGS.System) AND (BOOKINGS_NEW.strProject = BOOKINGS.strProject)) INNER JOIN PROJECTMASTER ON BOOKINGS.strProject = PROJECTMASTER.strProject
WHERE (((BOOKINGS_NEW.P4100U)>0) AND ((PROJECTMASTER.[T$CCAM])="50") AND ((BOOKINGS.FacePrice)>0) AND ((BOOKINGS.Est_Material)>0) AND (([Est_Margin]/[FacePrice])>0.25) AND ((PROJECTMASTER.[T$OPRJ]) Is Null) AND ((BOOKINGS.Sequ_Nbr)=0) AND ((BOOKINGS_NEW.Cancelled)=False) AND ((BOOKINGS_NEW.BookPeriod)>200800) AND ((BOOKINGS_NEW.P4004)=0) AND ((BOOKINGS_NEW.P4005)=0) AND ((BOOKINGS_NEW.P4006)=0) AND ((BOOKINGS_NEW.P4008)=0) AND ((BOOKINGS_NEW.P4010)=0) AND ((BOOKINGS_NEW.TSW)=0))
ORDER BY [FacePrice]/[P4100U];

Doesn't work:

SELECT BOOKINGS.strProject, PROJECTMASTER.prjDescription, BOOKINGS_NEW.P4100U, PROJECTMASTER.[T$CCAM], BOOKINGS.FacePrice, [FacePrice]/[P4100U] AS AVGFP, BOOKINGS.Est_Material, BOOKINGS.Est_Margin, [Est_Margin]/[FacePrice] AS EMP, PROJECTMASTER.INSTALL, PROJECTMASTER.[T$ITBP], PROJECTMASTER.[T$CUSTOMER]
FROM (BOOKINGS_NEW INNER JOIN BOOKINGS ON (BOOKINGS_NEW.BookPeriod = BOOKINGS.BookPeriod) AND (BOOKINGS_NEW.System = BOOKINGS.System) AND (BOOKINGS_NEW.strProject = BOOKINGS.strProject)) INNER JOIN PROJECTMASTER ON BOOKINGS.strProject = PROJECTMASTER.strProject
WHERE (((BOOKINGS_NEW.P4100U)>0) AND ((PROJECTMASTER.[T$CCAM])="50") AND ((BOOKINGS.FacePrice)>0) AND (([FacePrice]/[P4100U])>20000) AND ((BOOKINGS.Est_Material)>0) AND (([Est_Margin]/[FacePrice])>0.25) AND ((PROJECTMASTER.[T$OPRJ]) Is Null) AND ((BOOKINGS.Sequ_Nbr)=0) AND ((BOOKINGS_NEW.Cancelled)=False) AND ((BOOKINGS_NEW.BookPeriod)>200800) AND ((BOOKINGS_NEW.P4004)=0) AND ((BOOKINGS_NEW.P4005)=0) AND ((BOOKINGS_NEW.P4006)=0) AND ((BOOKINGS_NEW.P4008)=0) AND ((BOOKINGS_NEW.P4010)=0) AND ((BOOKINGS_NEW.TSW)=0))
ORDER BY [FacePrice]/[P4100U];

 

[vbaInet]

Looks like the problem you're having is with this part:

[Est_Margin]/[FacePrice] AS EMP

So you need another condition of [FacePrice] > 0

If you don't want that option because you would want to include Null FacePrice, then just wrap FacePrice in an IIF() condition testing for zero and making it 1.