problem with simple pct calculation

[psimon88]

I must be an idiot. I run this query in an ADP and get zeroes for the values on pct:



SELECT dbo.qry_sales_30days2.date, dbo.qry_sales_30days2.[count] AS quals, dbo.qry_sales_30days4.[count] AS sales, (dbo.qry_sales_30days4.[count]/dbo.qry_sales_30days2.[count]) as pct
FROM dbo.qry_sales_30days2 INNER JOIN
dbo.qry_sales_30days4 ON dbo.qry_sales_30days2.date = dbo.qry_sales_30days4.date

returns

DATE leads sales pct
Aug 6 2005 451 88 0

What's the command to format this pct so something other than 0 or 0.00 shows up?

Thanks.

 

[Crowley16]

iif(pct=0, "n/a", pct)

--------------------
Procrastinate Now!

 

[psimon88]

Thanks.

I'm not sure that that's it. It's not a matter of dividing by zero. All of the denominators have values.

 

[jebry]

Hi!

You can try this:

Format((dbo.qry_sales_30days4.[count]/dbo.qry_sales_30days2.[count]),"Percent")

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[psimon88]

'Format' is not a recognized function name.

This is an Access ADP file.

 

[psimon88]

I solved the problem by modifying the field in a previous query.

alter view qry_sales_30days2
as SELECT LEFT(date, 11) AS date, cast(COUNT(lApplicationID) as decimal) AS [quals]
FROM dbo.qry_sales_30days1
GROUP BY LEFT(date, 11)

The key was using lapplicationid as a decimal. Evidently, the application doesn't like dividing by an integer. Weird.

Thanks, all.