Hi,

I am using Ultra Dev 4.0 and Access 2000.
I have created a SQL request under Access and it works fine but when I try to use it under Ultra Dev It didn't work.
Here is the SQL request :
SELECT Courses2001.Clef, Pilotes.Nom, Sum(Nz([Courses2001]![POINTSD1],0)) AS TotalD1, Sum(Nz([Courses2001]![POINTSD2]-30,0)) AS TotalD2, Sum(Nz([Courses2001]![POINTSD1],0)+Nz([Courses2001]![POINTSD2]-30,0)) AS TotalD1D2, Sum((Nz([Courses2001]![PENALITED1],0))+(Nz([Courses2001]![PENALITED2],0))) AS TotalPENALITE, (Sum(Nz([Courses2001]![POINTSD1],0)+Nz([Courses2001]![POINTSD2]-30,0)))-(Sum((Nz([Courses2001]![PENALITED1],0))+(Nz([Courses2001]![PENALITED2],0)))) AS TotalGeneral, Count(Courses2001.POINTSD1) AS CompteDePOINTSD1, Count(Courses2001.POINTSD2) AS CompteDePOINTSD2, Count(Courses2001.F) AS CompteDeF
FROM Courses2001 INNER JOIN Pilotes ON Courses2001.Clef = Pilotes.Clef
GROUP BY Courses2001.Clef, Pilotes.Nom
ORDER BY Sum(Nz([Courses2001]![POINTSD1],0)+Nz([Courses2001]![POINTSD2]-30,0)) DESC;

Here is the error in ultra dev
Nz functin not defined in th expression

There is Null values in my database. I have to make Sum between two colums where there is null value, so I have to give zero value for all null value.

Thanks for your help, sorry for my english (I am french...)

hmm, well, as the error message says, Where is Nz Defined?

Instead of going through all of this trouble, why don't you do an update of all null fields to make them zero?
then you can make the field have a default of zero if a null tries to be inserted.

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

There is a good reason to have fields with Null Values. I need to have it. In fact, I have understood that the Nz function works only under Access. So it's way to give a value to null that I need.

I don't know if it works with access, but I use the Isnull(fieldName, 0) function all the time with SQL Server. See if that works.

if it doesn't, I know that Immediate if's work with access, I think the syntax is like this.

SELECT iif(FieldName is null, 0, FieldName) FROM table

I may be wrong on that, so consult documentation.

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I don't know if it works with access, but I use the Isnull(fieldName, 0) function all the time with SQL Server. See if that works.

if it doesn't, I know that Immediate if's work with access, I think the syntax is like this.

SELECT iif(FieldName is null, 0, FieldName) FROM table

I may be wrong on that, so consult documentation.

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Sorry, the precedent message was a mistake...

thanks for your asnwer, it was almost right. I have found this, which works fine, for anyone who's interested :
iif(Not IsNull([fieldName]),[FieldName],0) (anything can be place as the "0"...)