Run-time Error '94' Invalid Use Of Null - All Of Sudden Appears, Access 2007 (1 Viewer)

[accessNator]

For some reason, this error appears: Run-time error '94' Invalid use of Null
This has all of the sudden appeared and nothing has changed. Here is the following code below.

Private Sub SummarizeIntrastateFilersElectionStatus()

    Dim db As DAO.Database
    Set db = CurrentDb
    
    'Dim Table1 As String
    Table1 = "tblKusfFundData"
    
    Dim Query1 As String
    Query1 = "qryWorkSheetHistoryDateLookup"
    
    Dim Query2 As String
    Query2 = "qryWorksheetKUSFFundDataTbl"
    
    On Error Resume Next

    ' Delete Querie(s) If exist
    DoCmd.DeleteObject acQuery, Query1
    DoCmd.DeleteObject acQuery, Query2
    On Error GoTo 0
    
    Dim qdf1 As DAO.QueryDef
    Dim qdf2 As DAO.QueryDef
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strSQL1 As String
    Dim strSQL2 As String
    
    Dim passCID As String
    Dim beginDate As String
    Dim endDate As String
    
    ' Form Controls
    passCID = Me.txtInputCompanyId
           
    beginDate = "3/1/" & Me.txtPlanYear
    endDate = DateAdd("yyyy", 1, beginDate)
       
    '=========== Retreive the most recent record
    ' Create Sql String For Query1
    strSQL1 = "SELECT Max(wid) AS MaxOfwid, period_start "
    strSQL1 = strSQL1 & "FROM " & Table1 & " "
    strSQL1 = strSQL1 & "WHERE ((cid = " & passCID & ") AND ((period_start)>=#" & beginDate & "#) AND ((period_end)<#" & endDate & "#)) "
    strSQL1 = strSQL1 & "GROUP BY period_start;"
    
    '=========== Sum Net Intrastate Revenue based on the records retreived by Query 1
    ' Create Sql String For Query2
    strSQL2 = "SELECT 1 as id, sum(net_intrastate_revenue) as net_is_revenue "
    strSQL2 = strSQL2 & "FROM " & Query1 & " As A "
    strSQL2 = strSQL2 & "INNER JOIN " & Table1 & " As B ON A.MaxOfwid = B.wid AND A.period_start = B.period_start "
    strSQL2 = strSQL2 & "WHERE ((cid = " & passCID & ") AND ((B.period_start)>=#" & beginDate & "#) AND ((period_end)<#" & endDate & "#));"
    
    ' Initialize Query
    Set qdf1 = db.CreateQueryDef(Query1, strSQL1)
    Set rst1 = qdf1.OpenRecordset(dbOpenDynaset)

    ' Initialize Query
    Set qdf2 = db.CreateQueryDef(Query2, strSQL2)
    Set rst2 = qdf2.OpenRecordset(dbOpenDynaset)
    
    Call CalcAll

    Total_NetIS = DLookup("net_is_revenue", "qryWorksheetKUSFFundDataTbl", "ID = 1")
   
    Me.txtInputNetIntrastateTotalYear = Total_NetIS

    
    qdf1.Close
    qdf2.Close
    
    Set rst1 = Nothing
    Set rst2 = Nothing
    Set db = Nothing
    
End Sub

When I get the error window and choose to debug, the following statement is where the error points to.

Total_NetIS = DLookup("net_is_revenue", "qryWorksheetKUSFFundDataTbl", "ID = 1")

I know how to fix the error by using a NZ function

Total_NetIS = NZ(DLookup("net_is_revenue", "qryWorksheetKUSFFundDataTbl", "ID = 1"),0)

But my question is why all the sudden this is happening now?

What I have done to keep the original code intact to recreate it where the error doesn't happen is this.

1. Use .accdb file and create .accde file
2. Run .accde file and error is produced.
3. Open the .accde file and relink the backend DB and save .accde file.
4. Run .accde file and no-error is generated.

Obviously, I should put in the NZ in the .accdb file and recreate .accde, but I am trying to get a grasp, why all the sudden this error is generated when prior it ran fine.

Thoughts?

 

[Uncle Gizmo]

From what you describe I think it is a known issue with MS access, the fact that code which "shouldn't run" actually runs quite successfully.

The way I understand it and I could well be wrong but no one knows really challenged me when I have mentioned this before! The way I understand it is that as you "programme" with MS access and save, MS access saves like layer after layer of code. In theory the sub layers should not be used, however sometimes code that has been superseded may well be run, in other words sometimes the "sub layer of code will run and cause strange effects like you are observing.

 

[ChrisO]

What is Total_NetIS?
Where is it defined?
What is its data type?

Chris.