Divide by zero error?

I have several situations where I encounter the following error:
[Microsoft][ODBC SQL SERVER DRIVER][SQL Server]Divide by zero error
encountered.
I'm sure this topic has been covered hundreds of times - but how would I
stop this error in the following query:
SELECT [Pharmacy Exp] / TotalMem AS PhExpPMPM
FROM Sheet1$
WHERE ([Pharmacy Exp] <> '') AND (Period = '3Q04') AND (Domicile LIKE
'%ct%' OR
Domicile LIKE '%ma%' OR
Domicile LIKE '%nh%' OR
Domicile LIKE '%me%' OR
Domicile LIKE '%ri%' OR
Domicile LIKE '%vt%' OR
Domicile LIKE '%ny%' OR
Domicile LIKE '%nj%')
This issues comes about because either [pharmacy exp] or TotalMem is either
null or zero. I don't want to exclude the data from my query (ie: using
where [pharmacy exp] <>'') but would rather show a zero for the data point.SELECT CASE (TotalMem WHEN 0 THEN 0
ELSE [Pharmacy Exp] / TotalMem END PhExpPMPM
FROM Sheet1$
WHERE ([Pharmacy Exp] <> '') AND (Period = '3Q04') AND (Domicile LIKE
'%ct%' OR
Domicile LIKE '%ma%' OR
Domicile LIKE '%nh%' OR
Domicile LIKE '%me%' OR
Domicile LIKE '%ri%' OR
Domicile LIKE '%vt%' OR
Domicile LIKE '%ny%' OR
Domicile LIKE '%nj%')
Jacco Schalkwijk
SQL Server MVP
"William" <da@.northernit.net> wrote in message
news:KlU3e.6431$6a5.1685@.twister.nyroc.rr.com...
>I have several situations where I encounter the following error:
> [Microsoft][ODBC SQL SERVER DRIVER][SQL Server]Divide by zero error
> encountered.
> I'm sure this topic has been covered hundreds of times - but how would I
> stop this error in the following query:
>
> SELECT [Pharmacy Exp] / TotalMem AS PhExpPMPM
> FROM Sheet1$
> WHERE ([Pharmacy Exp] <> '') AND (Period = '3Q04') AND (Domicile LIKE
> '%ct%' OR
> Domicile LIKE '%ma%' OR
> Domicile LIKE '%nh%' OR
> Domicile LIKE '%me%' OR
> Domicile LIKE '%ri%' OR
> Domicile LIKE '%vt%' OR
> Domicile LIKE '%ny%' OR
> Domicile LIKE '%nj%')
> This issues comes about because either [pharmacy exp] or TotalMem is
> either
> null or zero. I don't want to exclude the data from my query (ie: using
> where [pharmacy exp] <>'') but would rather show a zero for the data
> point.
>