Problem w/ IIf function

[Paul Bush]

I am trying to fix the following query so that if there is no value
exists then a 0 is returned. The original SQL string is:

SELECT Sheet.Drawing, Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])) AS
ADCN FROM ADCN INNER JOIN Sheet ON ADCN.RecordID = Sheet.RecordID WHERE
(Sheet.Drawing = 'XXXXX' AND (Sheet.SheetNumber = 0) GROUP BY
Sheet.Drawing;

How would I edit the SQL string above (ACCESS) so that it accomplishes
the same as the SQL server version:

SELECT COALESCE(MAX(Sheet.DCN), 'NONE') AS MaxOfDCN,
COALESCE(MAX(ADCN.ADCN), 0) AS MaxOfADCN, Sheet.SheetNumber FROM
Drawings INNER JOIN (ADCN INNER JOIN
Sheet ON ADCN.RecordID = Sheet.RecordID) ON Drawings.Drawing =
Sheet.Drawing WHERE (Sheet.Drawing = 'XXXXX') AND
(Sheet.SheetType = 'SH') GROUP BY Sheet.Drawing, Sheet.SheetType,
Sheet.SheetNumber

I have tried simply switching out the IIf and MAX but could not get it
to work. Does anyone have any suggestions or need further explanation?
I've spent hours on this!

 

[Tom Ellison]

Dear Paul:

I suggest:

SELECT Sheet.Drawing, Max(Nz([ADCN.ADCN], 0)) ADCN
FROM ADCN
INNER JOIN Sheet
ON ADCN.RecordID = Sheet.RecordID
WHERE Sheet.Drawing = 'XXXXX'
AND Sheet.SheetNumber = 0
GROUP BY Sheet.Drawing;

Tom Ellison

 

[Paul Bush]

Thanks Tom! I will try this in the morning when I get to work and let
you know how it turned out. What is the Nz referring to?

 

[Tom Ellison]

Dear Paul:

Nz is a built-in function. If the first argument is null, the value of the
second argument is substituted.

Tom Ellison

 

[Paul Bush]

I used the string you provided and get an error at runtime: Undefined
function 'Nz' in expression. I should have probably mentioned that I
am calling this query from an intranet web application developed in
ASP.NET.

 

[Paul Bush]

I have also tried altering my first query so that I perform a LEFT JOIN
so it's not an all-or-nothing result. I still can't get this to work.

 

[Tom Ellison]

Dear Paul:

I thought you originally said you were using Access. The Access functions
are not available outside Access.

The SQL Server functions ARE available whenever you use the SQL Server
engine. That's one reason it is probably very superior for ASP
applications.

Access Jet does not have a server style engine. It depends on the local
machine application for its interface. I don't believe ASP has an interface
that includes such functions.

Since you're using this inside an aggregate function, there is no real
substitute I can see for having functions available. I strongly recommend
you stick with SQL Server or MSDE.

Tom Ellison