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

Answer 1

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

Answer 2

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

Answer 3

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!