Function in Access equivalent to COALESCE

0

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 use the COALESCE function, but I do not find this function in Access.

I usually use it as follows:

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

The query receives two parameters: Description and Initial Balance. For the column COD_CONTA the value to be entered would be "Greater Code + 1". The COALESCE function is used to test if the return value is NULL, will take the second argument "0" and add it with "1" otherwise it will be the value found in the table.

I've used Iif and even worked in SELECT:

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

It worked perfect, but when I put it in the previous query it gave error:

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:   ErrorMessage: Unspecified error

You could use another query to get the largest code and add up. But everything in a query is only more practical. I do not know what to do.

I'm using DataBase.Net to write the database and test the queries. I created an Access.mdb file.

    
asked by anonymous 31.07.2016 / 21:07

1 answer

1

I tested your include 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 Access Nz 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

    
02.08.2016 / 15:50