Access function equivalent to COALESCE

Question

Hello. I’m trying to build an INSERT with auto numbering in Access, but I’m not getting it. In SQL Server, Mysql and Postgres I use the COALESCE function, but I can’t find this function in Access.

Usually use as follows:

INSERT INTO CONTA(COD_CONTA, DESCRICAO, SALDO_INICIAL)
    VALUES ((SELECT COALESCE((MAX(COD_CONTA), 0) + 1) FROM CONTA), @Descricao, @SaldoInicial);

The query takes two parameters: Description and Saldoinicial. For the COD_CONTA column the value to be inserted would be "Majorcode + 1". The function COALESCE use to test if the return value is NULL, will take the second argument "0" and add with "1" otherwise it will be the value found in the table.

I used iif and it even worked on SELECT:

SELECT IIF(ISNULL(MAX(COD_CONTA)), 0, MAX(COD_CONTA)) + 1 FROM CONTA;

It worked perfect, but when I put in the previous query it was wrong:

INSERT INTO CONTA(COD_CONTA, DESCRICAO, SALDO_INICIAL)
VALUES ((SELECT IIF(ISNULL(MAX(COD_CONTA)), 0, MAX(COD_CONTA)) + 1 FROM CONTA), @Descricao, @SaldoInicial)

Errorcode: -2147467259 [Microsoft JET Database Engine] Nativeerror: 0, Sqlstate: Errormssage: Error not specified

You could use another query to take the biggest code and add it up. But everything in a query is only more practical. I don’t know what to do.

I am using Database.Net to write the database and test the queries. I have created an Access.mdb file.

Answer 1

I tested your inclusion script and found an extra parenthesis after the word COALESCE. Making the change would look like this:

INSERT INTO CONTA(COD_CONTA, DESCRICAO, SALDO_INICIAL)
VALUES ((SELECT COALESCE(MAX(COD_CONTA), 0) + 1 FROM CONTA),  @Descricao, @SaldoInicial);

The Nz Access function could be used in this case. The syntax is as follows::

Nz ( variant [, valueifnull ] )

Try: SELECT Nz(MAX(COD_CONTA), 0) + 1 FROM ACCOUNT

More Information here