Microsoft Access: VBA & Check NULL fields & ...

1. Links

Examples of expressions that check for null values
http://office.microsoft.com/en-ca/access-help/examples-of-expressions-that-check-for-null-values-HP001099035.aspx

Nz Function
http://office.microsoft.com/en-us/access-help/nz-function-HA001228890.aspx

http://www.techonthenet.com/access/functions/index.php
http://www.techonthenet.com/access/modules/recordset.php

Introduction to Microsoft Access and VBA
http://www.functionx.com/vbaccess/Lesson01.htm

MS Access: Database variable "not defined" error in Access 2003/XP/2000/97
http://www.techonthenet.com/access/questions/db_object.php

2. Details

For access 2007 - 2010, go to Database Tools/Visual Basic
Right click to Modules and select Insert\Module
Edit the code as below example
Save as module name

On the form or query, just call GetStatus() example:
=GetStatus([HistorySWT1];[HistorySWT2];[HistorySWT3])

3. Example

=IIf(IsNull([SWT1History]);"";[SWT1History])

VBA example:

Option Compare Database

Option Explicit

Function GetStatus(sHisSWT1, sHisSWT2, sHisSWT3 As String) As String

    Dim sStatus As String

If IsNull(sHisSWT1) Or IsNull(sHisSWT1) Or IsNull(sHisSWT1) Then

    GetStatus = "++++++++++++"

Else

    If sHisSWT1 <> "" Then

        sStatus = sHisSWT1 + " - "

    End If

   

    If sHisSWT2 <> "" Then

        sStatus = sStatus + sHisSWT2 + " - " ' + sHisSWT3

    End If

   

    sStatus = sHisSWT1 + " - " + sHisSWT2 + " - " + sHisSWT3

   

   

        If sHisSWT3 = "Reopened" Then

            sStatus = "Open"

        Else

            If (sHisSWT2 = "Solved") Or (sHisSWT2 = "Identified, Solved") Then

                sStatus = "Closed"

            Else

                If sHisSWT1 = "Identified" Then

                    sStatus = "Identified"

                Else

                    sStatus = "~"

                End If

            End If

        End If

       

    GetStatus = sStatus

End If

End Function