Using ISNULL function is throwing an error

user_13655593 0
Viewed 165

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.

ms-access sql winforms database c#
1 Answers

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))
user_11440350
Related