IF Then statement in MS Access Passthrough SQL Query

[James C.]

I am getting a "Division by zero error" in my pass-through query.

Here is my Select statement

SELECT
CD_CODE,(SUM(HP_SYSTEM_EXPECTED_BOOKINGS)/SUM(TOTAL_NET_BOOKINGS))*100 AS
SYSTEMSHARE

The error is because of a zero sum of Total_Net_Bookings.

How can I put a IF statement here to say if Sum(Total_Net_Bookings) = 0,
then SystemShare = 0 else ......

Any ideas?

 

[Lynn Trapp]

Give this a try:

IIF(SUM(TOTAL_NET_BOOKINGS)<> 0,
SUM(HP_SYSTEM_EXPECTED_BOOKINGS)/SUM(TOTAL_NET_BOOKINGS),0)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Lynn Trapp]

Oops, my bad. I didn't notice that you were referring to a pass through
query. You will probably need to use your server databases equivalent of the
NZ function.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Ofer]

If you work with MSSql then you need to use the case statement
CASE SUM(TOTAL_NET_BOOKINGS)
WHEN NULL THEN 0
ELSE
SUM(HP_SYSTEM_EXPECTED_BOOKINGS)/SUM(TOTAL_NET_BOOKINGS)*100
END AS SYSTEMSHARE