CODEWITHSUNDEEP
c#sqldatabasewinformsms-access
Muhammad Rizwan
Muhammad Rizwan

Reputation: 141

Using ISNULL function is throwing an error

Table Fields are, RollNo DataType is Number and Class DataType is ShortText.

This the query,

SELECT 
    ISNULL(MAX(RollNo), -1) AS Roll 
FROM 
    Students 
WHERE
    Class = "One"

When I run this query, I get an error:

Invalid operation or syntax using multi-valued field

I want to get a value of -1 if the column is null, otherwise the maximum value of the RollNo with the where condition of Class being One.

Upvotes: 2

Views: 427

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

isnull() returns a boolean value. It is really never used, because is null is the standard and also supported by MS Access.

I think you want nz() -- to replace the null value with -1:

select nz(max(rollno), -1)

If you want to return NULL if the value is -1, then use iif():

select iif(max(rollno) = -1, null, max(rollno))

Upvotes: 3

Related Questions