Problem w/ IIf function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul Bush - 22 Nov 2005 23:48 GMT

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!

Reply to this Message

Tom Ellison - 23 Nov 2005 04:06 GMT

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

>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:
[quoted text clipped - 18 lines]
> to work. Does anyone have any suggestions or need further explanation?
> I've spent hours on this!

Reply to this Message

Paul Bush - 23 Nov 2005 04:42 GMT

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?

Reply to this Message

Tom Ellison - 23 Nov 2005 04:52 GMT

Dear Paul:

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

Tom Ellison

> 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?

Reply to this Message

Paul Bush - 23 Nov 2005 16:21 GMT

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.

Reply to this Message

Paul Bush - 23 Nov 2005 16:29 GMT

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.

Reply to this Message

Tom Ellison - 23 Nov 2005 19:53 GMT

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

>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.

Reply to this Message