none
Using 'is null' in microsoft access vba

    Question

  • Hi.  An MVP helped me with this code below.  But I need to tweak it a bit.  My field prjParentProjectID is a text field.  I need the code to say, if prjParentProjectID is not null then open rptProjectedCostSummaryChild otherwise open rptProjectedCostSummary.  Thank you very much!

    Private Sub cmdOpen_ProjectedCostSummaryReport_Click()
    On Error GoTo Err_cmdOpen_ProjectedCostSummaryReport_Click

    If prjParentProjectID = True Then
        DoCmd.OpenReport "rptProjectedCostSummaryChild", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
     Else   
        DoCmd.OpenReport "rptProjectedCostSummary", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
    End If

    Exit_cmdOpen_ProjectedCostSummaryReport_Click:
        Exit Sub

    Err_cmdOpen_ProjectedCostSummaryReport_Click:
        Resume Exit_cmdOpen_ProjectedCostSummaryReport_Click
       
    End Sub

    Tuesday, June 15, 2010 3:45 PM

Answers

  • Hi Stacey,

    Here is the code as you asked.

    If Not IsNull(prjParentProjectID) Then
        DoCmd.OpenReport "rptProjectedCostSummaryChild", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
     Else   
        DoCmd.OpenReport "rptProjectedCostSummary", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
    End If

    Personally, I find it a bit easier to read if I test for the positive condition rather then the negative condition.  Personally, I would write the code as:

    If IsNull(prjParentProjectID) Then
        DoCmd.OpenReport "rptProjectedCostSummary", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
    Else
        DoCmd.OpenReport "rptProjectedCostSummaryChild", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
    End If

    There is another piece of code I picked up that checks for both Null and zero lenght string.  As I understand it, Null and "" are TWO different values.  I believe that a string value of "" will fail the IsNull test.  Someone please correct me if I am wrong.

    Here is a usefull function call IsNothing as opposed to IsNull.  It is used just like IsNull but it test for more conditions.

    Public Function IsNothing(ByVal varValueToTest) As Integer
    '-----------------------------------------------------------
    ' Does a "nothing" test based on data type.
    '   Null = nothing
    '   Empty = nothing
    '   Number = 0 is nothing
    '   String = "" is nothing
    '   Date/Time is never nothing
    ' Inputs: A value to test for logical "nothing"
    ' Outputs: True = value passed is a logical "nothing", False = it ain't
    ' Created By: JLV 01/31/95
    ' Last Revised: JLV 01/31/95
    '-----------------------------------------------------------
    Dim intSuccess As Integer

        On Error GoTo IsNothing_Err
        IsNothing = True

        Select Case varType(varValueToTest)
            Case 0      ' Empty
                GoTo IsNothing_Exit
            Case 1      ' Null
                GoTo IsNothing_Exit
            Case 2, 3, 4, 5, 6  ' Integer, Long, Single, Double, Currency
                If varValueToTest <> 0 Then IsNothing = False
            Case 7      ' Date / Time
                IsNothing = False
            Case 8      ' String
                If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
        End Select


    IsNothing_Exit:
        On Error GoTo 0
        Exit Function

    IsNothing_Err:
        IsNothing = True
        Resume IsNothing_Exit

    End Function

     

    Good luck,

    Dennis

    Tuesday, June 15, 2010 4:16 PM
  • You are right, an empty string, or a zero length string is technically not the same as a database NULL value which stands for Not Available, unknown, not applicable, no match, etc. An empty string is a string with no character, yet, in it.

     

    You can test for both an empty string AND a null value with:

     

    len( stringVariable & vbNullString)  = 0

     

    Note that vbNullString is a predefined empty string (a somewhat 'optimized' one), but you could use ""   as well, for the example here up.

     

     

    Some confusion may occur when an ACCESS form has to display a NULL: the text box displays a blank 'cell', which is correctly understood back as a NULL when this text box is 'saved' in a table, but COULD have been an empty string too.  So while it may bring confusion (a null, an empty string, and a string full of spaces  look the same, in a text box), the context saves us, without much problem.

     

     

    Vanderghast, Access MVP

    Tuesday, June 15, 2010 5:12 PM
    Moderator

All replies

  • Hi Stacey,

    Here is the code as you asked.

    If Not IsNull(prjParentProjectID) Then
        DoCmd.OpenReport "rptProjectedCostSummaryChild", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
     Else   
        DoCmd.OpenReport "rptProjectedCostSummary", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
    End If

    Personally, I find it a bit easier to read if I test for the positive condition rather then the negative condition.  Personally, I would write the code as:

    If IsNull(prjParentProjectID) Then
        DoCmd.OpenReport "rptProjectedCostSummary", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
    Else
        DoCmd.OpenReport "rptProjectedCostSummaryChild", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
    End If

    There is another piece of code I picked up that checks for both Null and zero lenght string.  As I understand it, Null and "" are TWO different values.  I believe that a string value of "" will fail the IsNull test.  Someone please correct me if I am wrong.

    Here is a usefull function call IsNothing as opposed to IsNull.  It is used just like IsNull but it test for more conditions.

    Public Function IsNothing(ByVal varValueToTest) As Integer
    '-----------------------------------------------------------
    ' Does a "nothing" test based on data type.
    '   Null = nothing
    '   Empty = nothing
    '   Number = 0 is nothing
    '   String = "" is nothing
    '   Date/Time is never nothing
    ' Inputs: A value to test for logical "nothing"
    ' Outputs: True = value passed is a logical "nothing", False = it ain't
    ' Created By: JLV 01/31/95
    ' Last Revised: JLV 01/31/95
    '-----------------------------------------------------------
    Dim intSuccess As Integer

        On Error GoTo IsNothing_Err
        IsNothing = True

        Select Case varType(varValueToTest)
            Case 0      ' Empty
                GoTo IsNothing_Exit
            Case 1      ' Null
                GoTo IsNothing_Exit
            Case 2, 3, 4, 5, 6  ' Integer, Long, Single, Double, Currency
                If varValueToTest <> 0 Then IsNothing = False
            Case 7      ' Date / Time
                IsNothing = False
            Case 8      ' String
                If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
        End Select


    IsNothing_Exit:
        On Error GoTo 0
        Exit Function

    IsNothing_Err:
        IsNothing = True
        Resume IsNothing_Exit

    End Function

     

    Good luck,

    Dennis

    Tuesday, June 15, 2010 4:16 PM
  • You are right, an empty string, or a zero length string is technically not the same as a database NULL value which stands for Not Available, unknown, not applicable, no match, etc. An empty string is a string with no character, yet, in it.

     

    You can test for both an empty string AND a null value with:

     

    len( stringVariable & vbNullString)  = 0

     

    Note that vbNullString is a predefined empty string (a somewhat 'optimized' one), but you could use ""   as well, for the example here up.

     

     

    Some confusion may occur when an ACCESS form has to display a NULL: the text box displays a blank 'cell', which is correctly understood back as a NULL when this text box is 'saved' in a table, but COULD have been an empty string too.  So while it may bring confusion (a null, an empty string, and a string full of spaces  look the same, in a text box), the context saves us, without much problem.

     

     

    Vanderghast, Access MVP

    Tuesday, June 15, 2010 5:12 PM
    Moderator
  • So sorry to post again on this thread, but I can't get the code to work! Here is what I pasted into my form:

    Private Sub cmdOpen_ProjectedCostSummaryReport_Click()
    On Error GoTo Err_cmdOpen_ProjectedCostSummaryReport_Click

    If IsNull(prjParentProjectID) Then
        DoCmd.OpenReport "rptProjectedCostSummary", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
    Else
        DoCmd.OpenReport "rptProjectedCostSummaryChild", acViewPreview, "", "[budCCPID]=[Forms]![flkpProjects]![listbox_Projects]", acNormal
    End If

    Exit_cmdOpen_ProjectedCostSummaryReport_Click:
        Exit Sub

    Err_cmdOpen_ProjectedCostSummaryReport_Click:
        Resume Exit_cmdOpen_ProjectedCostSummaryReport_Click
       
    End Sub

    I also pasted in the null checker:

    Public Function IsNothing(ByVal varValueToTest) As Integer
    '-----------------------------------------------------------
    ' Does a "nothing" test based on data type.
    '   Null = nothing
    '   Empty = nothing
    '   Number = 0 is nothing
    '   String = "" is nothing
    '   Date/Time is never nothing
    ' Inputs: A value to test for logical "nothing"
    ' Outputs: True = value passed is a logical "nothing", False = it ain't
    ' Created By: JLV 01/31/95
    ' Last Revised: JLV 01/31/95
    '-----------------------------------------------------------
    Dim intSuccess As Integer

        On Error GoTo IsNothing_Err
        IsNothing = True

        Select Case VarType(varValueToTest)
            Case 0      ' Empty
                GoTo IsNothing_Exit
            Case 1      ' Null
                GoTo IsNothing_Exit
            Case 2, 3, 4, 5, 6  ' Integer, Long, Single, Double, Currency
                If varValueToTest <> 0 Then IsNothing = False
            Case 7      ' Date / Time
                IsNothing = False
            Case 8      ' String
                If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
        End Select


    IsNothing_Exit:
        On Error GoTo 0
        Exit Function

    IsNothing_Err:
        IsNothing = True
        Resume IsNothing_Exit

    End Function

     

    Is there something I am missing? When I click my button to run the report it only runs the regular Projected Cost Summary report. It does not run the Projected Cost Summary Child report when I select a project project that has a value in prjParentProjectID.

     

    Any ideas? Thank you ever so much!

     

    Friday, July 16, 2010 10:44 PM