[RESOLVED] [ACCESS] IsNull() function in Access

[rafaelcb]

Hello,

I'm using Access 2010 and I'd like to get a specific value from my query when some null value is obtained. I know that in SQL Server we can use the function IsNull, but this doesn't work with Access. Could someone help me?

I tried to use Nz function and IIF(IsNull(...), if true, if false), but anyone worked.

Thanks.

[IkkeEnGij]

AFAIK:
IsNull() is a vba function,that returns true or false
Nz() is an access function that returns the second argument if the first argument is null
or the value of the first argument if it is not null, (simular to SQL IsNull)

[rafaelcb]

Hello IkkeEnGij,

I tryed to use Nz function too, but this doesn't worked. I made something like below:

SELECT Nz(tblTermos.Termo, tblTermos.Termo, 'Null value') FROM tblTermos

I'd like to use a function like the IsNull from SQL Server.

Could you help me? Have something wrong in my query above?

[IkkeEnGij]

I have not the faintest idea what you are trying to do
the Nz() has only 2 parameters

[rafaelcb]

The Nz function works in Access like the IsNull works in SQL Server, right?

[IkkeEnGij]

cant test it, but i think YES

[dmaruca]

Use the IS NULL keywords... IIF([Column] IS NULL, 'It was null', [Column])

[techgnome]

The Nz function works in Access like the IsNull works in SQL Server, right?

yes... and the IsNull in SQL Server still only takes TWO parameters....

-- Access
SELECT Nz(tblTermos.Termo, 'Null value') FROM tblTermos 

-- SQL Server
SELECT IsNull(tblTermos.Termo, 'Null value') FROM tblTermos

-tg

[IkkeEnGij]

since there is nothing that is equal to NULL (not even NULL)
'IS NULL' is not needed if you use IIf()

IIF([Column],[Column],"It was null")

[dmaruca]

since there is nothing that is equal to NULL (not even NULL)
'IS NULL' is not needed if you use IIf()

IIF([Column],[Column],"It was null")

In your example it could also be false. IS NULL is there so you don't use "[Column]=Null". (I'm not trying to quibble)

http://www.techrepublic.com/article/...ull-in-access/

[techgnome]

In your example it could also be false. IS NULL is there so you don't use "[Column]=Null". (I'm not trying to quibble)

http://www.techrepublic.com/article/...ull-in-access/

I will quibble... it's there because nothing can = null. that's because null is the absence of a value. so [column] = null doesn't work. But Is Null does... Things are NULL or they are not... but they do not equal null. IIF may work, but experience has taught me that it doens't work as well as most people would expect. NZ in Access and IsNull in (most) other DBMSs or some form of Is Null does.

-tg

[dmaruca]

I will quibble... it's there because nothing can = null. that's because null is the absence of a value. so [column] = null doesn't work. But Is Null does... Things are NULL or they are not... but they do not equal null. IIF may work, but experience has taught me that it doens't work as well as most people would expect. NZ in Access and IsNull in (most) other DBMSs or some form of Is Null does.

-tg

Hi techgnome. I wasn't advocating the use of =Null Have a great night.

[techgnome]

Oh I know that... I just wanted to quibble and point that that coulmn = null doesn't work, period. Is Null is there not so that you don't have to use = null... but because you can't.

-tg

[rafaelcb]

Hello,

Now I made my query like...

SELECT Nz(tblTermos.Termo, 'Null value') FROM tblTermos

and this worked! But when I try to make the same query in VBA of Excel, this doesn't work. The error number -2147217900 is showed.

I'm making tests...

[techgnome]

NZ is an ACCESS function... it doesn't work anywhere else. If you're using VBA, then you use the IsNull function.
It has a little different syntax: it takes a single parameter and returns True or False depending if the expression is null or not.

If IsNull(myVar) then
  Msgbox "It's NULL!"
else"
  Msgbox "Not null!"
End if

-tg

[rafaelcb]

Hello tg,

I'm not trying to use Nz() function in VBA. I make my query in VBA code and execute this using ADO Command / Recordset / Connection.

See below the code I made:

Private Sub AtualizarLista()
On Error GoTo TrataErro

    Dim cnn As ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rst As ADODB.Recordset
    Dim Query As String
    Dim Where As Boolean
    Dim li As ListItem
    Dim i As Integer
    
    Set cnn = ConectaBanco
    Set cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdText
 
    Query = "SELECT "
    Query = Query & "tblGrupos.Descricao, tblTermos.Termo, "
    Query = Query & "Nz(tblAbreviacoes.Abreviacao, 'Null value'), tblTermos.Verificar, "
    Query = Query & "tblTermos.Excluido "
    Query = Query & "FROM "
    Query = Query & "(tblGrupos INNER JOIN tblTermos ON tblGrupos.id = tblTermos.id_Grupo) "
    Query = Query & "LEFT JOIN ("
    Query = Query & "SELECT tblAbreviacoes.id_Termo, tblAbreviacoes.Abreviacao FROM tblAbreviacoes "
    Query = Query & "INNER JOIN ("
    Query = Query & "SELECT tblAbrevAux.id_Termo, MAX(tblAbrevAux.Alterado) AS AlteradoAux FROM tblAbreviacoes tblAbrevAux GROUP BY tblAbrevAux.id_Termo) tblAbrevAux "
    Query = Query & "ON tblAbrevAux.id_Termo = tblAbreviacoes.id_Termo AND tblAbrevAux.AlteradoAux = tblAbreviacoes.Alterado) tblAbrevAux2 "
    Query = Query & "ON tblAbrevAux2.id_Termo = tblTermos.id "

    If trim(UCase(txtTermo.Value)) <> "" Then
        TrataWhere Where, Query
        Query = Query & "tblTermos.Termo like '%" & trim(UCase(txtTermo.Value)) & "%' "
    End If
        
    If chkboxSemAbreviacao.Value = True Then
        TrataWhere Where, Query
        Query = Query & "(tblAbreviacoes.Abreviacao IS NULL OR tblAbreviacoes.Abreviacao = '')"
    ElseIf trim(UCase(txtAbreviacao.Value)) <> "" Then
        TrataWhere Where, Query
        Query = Query & "tblAbreviacoes.Abreviacao like '%" & trim(UCase(txtAbreviacao.Value)) & "%' "
    End If
        
    If trim(UCase(cboxGrupo.Value)) <> "" Then
        cmd.CommandText = "SELECT tblGrupos.id FROM tblGrupos WHERE tblGrupos.Descricao = '" & trim(UCase(cboxGrupo.Value)) & "'"
        Set rst = cmd.Execute
        
        TrataWhere Where, Query
        Query = Query & "tblGrupos.id = " & rst.Fields("id").Value & " "
    End If
    
    If trim(UCase(cboxVerificar.Value)) <> "" Then
        TrataWhere Where, Query
        Query = Query & "tblTermos.Verificar = " & TrataVerificar(cboxVerificar.Value) & " "
    End If

    TrataWhere Where, Query
    Query = Query & "tblTermos.Excluido = False "

    Query = Query & "ORDER BY "
    Query = Query & "tblGrupos.id ASC, tblTermos.Termo ASC"

    Debug.Print Query

    cmd.CommandText = Query
    Set rst = cmd.Execute

    lview.ListItems.Clear
    
    i = 1
            
    Do Until rst.EOF
        Set li = lview.ListItems.Add(, , CStr(i))
        
        li.SubItems(1) = rst.Fields("Descricao").Value
        li.SubItems(2) = rst.Fields("Termo").Value
        
        If IsNull(rst.Fields("Abreviacao").Value) Then
            li.SubItems(3) = ""
        Else
            li.SubItems(3) = rst.Fields("Abreviacao").Value
        End If

        li.SubItems(4) = TrataVerificar(rst.Fields("Verificar").Value)
        
        i = i + 1
        rst.MoveNext
    Loop
    
    If lview.ListItems.Count <= 27 Then
        lview.ColumnHeaders.Item(4).Width = 125.25
        lview.ColumnHeaders.Item(3).Width = 141.75
    Else
        lview.ColumnHeaders.Item(4).Width = 119.5
        lview.ColumnHeaders.Item(3).Width = 136
    End If
    
    DesconectaBanco cnn, rst, cmd
    
    Exit Sub
    
TrataErro:

    TrataErro "Erro durante a execução do procedimento ""AtualizarLista"" do form ""frmEditar""."

End Sub

The query will be something like this:

SELECT tblGrupos.Descricao, tblTermos.id, tblTermos.Termo, Nz(tblAbreviacoes.Abreviacao, 'Null value'), tblTermos.Verificar, tblTermos.Excluido FROM (tblGrupos INNER JOIN tblTermos ON tblGrupos.id = tblTermos.id_Grupo) LEFT JOIN (SELECT tblAbreviacoes.id_Termo, tblAbreviacoes.Abreviacao FROM tblAbreviacoes INNER JOIN (SELECT tblAbrevAux.id_Termo, MAX(tblAbrevAux.Alterado) AS AlteradoAux FROM tblAbreviacoes tblAbrevAux GROUP BY tblAbrevAux.id_Termo) tblAbrevAux ON tblAbrevAux.id_Termo = tblAbreviacoes.id_Termo AND tblAbrevAux.AlteradoAux = tblAbreviacoes.Alterado) tblAbrevAux2 ON tblAbrevAux2.id_Termo = tblTermos.id WHERE tblTermos.Excluido = False ORDER BY tblGrupos.id ASC, tblTermos.Termo ASC

Note that in this VBA code above, I'm using the Nz() to build my query for after I execute. And when this is executed an error message is showed, but if I copy this query from VBA and paste this in Access, it works normally.

[techgnome]

If the query works in Access as is... then it maybe something else that's the problem. Your "error handler" covers the whole sub, so the error could be coming from anywhere in the code. It may be the query, it may not be. It doesn't help that your error trap is generic in nature.

-tg

[rafaelcb]

I know that my problem is when the line below is executed because I tested line by line my program.

Set rst = cmd.Execute

And this message is showed: "Nz Function undefined in expression".

[westconn1]

following the thread, nz is an access function, nothing to do with excel, you would need to use a generic VBA function or a valid SQL equivalent

you can use like
where s1.f1 is not null

[techgnome]

Shouldn't matter... it looks like he's running VBA code in Excel against an Access database... so as long as the connection is actually to an Access database, it should work. But I doin't see the connection string info, so who knows.

-tg

[IkkeEnGij]

In your example it could also be false. IS NULL is there so you don't use "[Column]=Null"

indeed, i did not think about booleans,my bad

[rafaelcb]

Hi,

My problem was resolved! I used:

Iif(IsNull(table.column), 'null value', table.column) AS Value1

... instead of Nz() function. I didn't use the above function correctly, and because this the code wasn't working.

Thanks for everyone!