How to Detect Null Value in Two Dimensional Array

[subedimite]

I have a two dimensional Array that is read from a recordset with the GetRows command. I wanted to know if the field in the Array is empty in column 1 and the 75th Record. When I debug print the field it returns Null but it seems like I can not check if the field in Null because the it is not going through the IF statement and the message box does not pop up.

 NoOfRecords = qryTestFailureRes.RecordCount 'Find out no of failures
    ArrayRecordsTestFailures = qryTestFailureRes.GetRows(NoOfRecords) 

        Debug.Print ArrayRecordsTestFailures(1, 75)
        If ArrayRecordsTestFailures(1, 75) = Null Then
            MsgBox "No Description exists in the database: Continue?"
                TestDescription = "Empty"
            Else
                TestDescription = ArrayRecordsTestFailures(FieldNum + 1, RecNum + i) 'Test Description
        End If

[TheSmileyCoder]

And your problem is?

[TheSmileyCoder]

I am a bit fuzzy on the comparing to null aspect, and whether there might be some oddities there.

Another approach however could be:

If IsNull(ArrayRecordsTestFailures(1, 75)) Then

or

Ff ArrayRecordsTestFailures(1, 75) & ""="" Then

[NeoPa]

This is one of those rare occasions where merging a duplicate thread makes some sense.

Please be warned for future reference that posting duplicate threads is not allowed and may lead to disciplinary measures.

[NeoPa]

If the array is Dimmed as Variant (remarkably you don't include that one piece of useful information) then you can use the IsNull() function as Smiley has indicated.

[ADezii]

I cannot understand why you would want to use a 2-Dimensional Array in this case, buy I'm sure that you must have your reasons. I created for you a Function that will do the job.

1. Pass to the Function a Valid Data Source in the Form of a Table Name, Query Name, or SQL Statement, as indicated below:
   

   Call fFindNullsInArray("qryEmployees")

2. Change the Value of the Constant conCOLUMN_NUM within the Function to indicate which Column you will check for Nulls. In this case it will be 0 (Column 1).
3. The Function will create the 2-Dimensional Array, and check the Value in the first Column, every Record, for any Nulls.
4. If any Nulls are found, Output will be directed to the Immediate Window.
   

   Public Function fFindNullsInArray(strDataSource)
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim varRet As Variant
   Dim intRowNum As Integer
   Const conCOLUMN_NUM As Byte = 0     '1st Column, Indexed at 0
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset(strDataSource, dbOpenSnapshot)
   
   If rst.BOF And rst.EOF Then Exit Function      '0 Records
   
   rst.MoveLast: rst.MoveFirst     'Need a Valid Record Count
   
   'Let's retrieve ALL Rows in the Data Source
   varRet = rst.GetRows(rst.RecordCount)
   
   For intRowNum = 0 To UBound(varRet, 2)        'Loop thru each Row
     'Analyze each Row - Column 1 only
     If IsNull(varRet(conCOLUMN_NUM, intRowNum)) Then
       Debug.Print "NULL Value in Column: " & (conCOLUMN_NUM + 1) & " | Row: " & (intRowNum + 1)
     End If
   Next
                                           
   rst.Close
   Set rst = Nothing
   End Function

5. Sample Output based on Test Data:
   

   NULL Value in Column: 1 | Row: 8
   NULL Value in Column: 1 | Row: 9

P.S. - For the sake of brevity and simplicity, I have omitted Error Checking in the Function Routine. You, on the other hand, should definitely incorporate it into the overall Logic.