Runtime error 94: Invalid use of Null

[KerryL]

I've been asked to look at this problem in a fairly complex database that I didn't write. Apparently this is the first time in 2-3 years this process has produced an error. Not being very strong with code, I thought I'd post this to see if you coding wizards spot something obvious.

For j = 1 To UBound(arData)
    strFieldNamePrecentDestination = arFieldNameCountDestination(j) + "_P"
    strSQL = "SELECT [Data for OCR].CEPD, [Data for OCR].[IF District], "
    strSQL = strSQL + " Count([Data for OCR].[VEDS Student ID]) AS TotalByType "
    strSQL = strSQL + " FROM [Data for OCR] "
    strSQL = strSQL + " WHERE [Data for OCR]." + strFieldName + "=" + strSqlSep + arData(j) + strSqlSep
    strSQL = strSQL + " AND [Data for OCR].CEPD=" + Str(rstTotal!CEPD)
    strSQL = strSQL + " AND [Data for OCR].[IF District]='" + rstTotal![IF District] + "'"
    strSQL = strSQL + " GROUP BY [Data for OCR].CEPD, [Data for OCR].[IF District]"
            
    Set rstTotalByType = DBEngine(0)(0).OpenRecordset(strSQL)
    If Not rstTotalByType.EOF Then intTotalByType = rstTotalByType!TotalByType
    Else
        intTotalByType = 0
    End If

If anyone has any ideas or can give me a good place to start, please let me know. Thank you in advance.

 

[AlexCuse]

I would first look here:

Set rstTotalByType = DBEngine(0)(0).OpenRecordset(strSQL)

My guess is that one of the values you are concatenating to your strSQL is null, and this causes the whole thing to be null. And of course, you can't OpenRecordset(NULL)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[JoeAtWork]

Any of the parts that start "rstTotal!" might be the culprit. You might be able to eliminate the error by surrounding those with an Nz function, but that may change the logic of your code, examples:

Nz(rstTotal!CEPD,"")
Nz(rstTotal![IF District],"")

The trouble is probably that one of the fields that should have data is empty. I would check the CEPD and [IF District] fields for blank values in whichever table rstTotal is based on.