VBA: Invalid use of Null error message

Hello, I am trying to correct code someone else created where fields and data types have changed since then. I believe this error, "Run-time error '94': Invalid use of Null" may have something to do with a data type (perhaps looking for string and can't be null) from googling. I am not sure how to correct this. I have tried verifying data types match and tables/fields exist even though some of these are temp records. I have placed where the error is occuring in bold. **edited** I have changed those dates in the code to one year earlier since I am testing a schedule that has already been created. Is there something I can replace on the year area so actual year doesn't matter, just days and months?

Private Sub Command0_Click()
    Dim rst As DAO.Recordset
    Dim dbS As DAO.Database
    Dim rst2 As DAO.Recordset
    Dim rst3 As DAO.Recordset
    Dim rst4 As DAO.Recordset
    Dim rst5 As DAO.Recordset
    Dim numInvs As Integer
    Dim tempInvDt As Date
    Dim x As Integer
    Dim BegDate As Date
    Dim EndDate As Date
    Dim tmpTableName As String
    Dim tmpRecName As String
    Dim strSQL As String
   
    DoCmd.SetWarnings False
    DoCmd.Hourglass True
   
   Set dbS = CurrentDb()
    Set rst = dbS.OpenRecordset("DMsThatNeed to be changed")
   
    Do Until rst.EOF
       
            numInvs = rst!RecFreq
            x = 1
            BegDate = CDate(rst!LastInvDate) + 90
           
            Do Until x > numInvs
                If numInvs = 4 Then
                    EndDate = BegDate + 90
                End If
                If numInvs = 3 Then
                    EndDate = BegDate + 110
                End If
                If numInvs = 2 Then
                    EndDate = BegDate + 170
                End If
                If x = 1 Then
                    EndDate = BegDate + 300
                End If
               
               ' new crazy code for 2 inventories
                If numInvs = 2 Then
                    If CDate(rst!LastInvDate) <= #8/18/2018# Then
                        If x = 1 Then
                            BegDate = #12/28/2018#
                            EndDate = #1/24/2019#
                        Else
                            BegDate = #7/12/2019#
                            EndDate = #8/8/2019#
                        End If
                    Else
                    If CDate(rst!LastInvDate) <= #9/15/2018# Then
                        If x = 1 Then
                            BegDate = #1/25/2019#
                            EndDate = #2/21/2019#
                        Else
                            BegDate = #8/9/2019#
                            EndDate = #9/5/2019#
                        End If
                    Else
                        If CDate(rst!LastInvDate) <= #10/9/2019# Then
                            If x = 1 Then
                                BegDate = #2/22/2019#
                                EndDate = #3/21/2019#
                            Else
                                BegDate = #9/6/2019#
                                EndDate = #10/3/2019#
                            End If
                    Else
                            If CDate(rst!LastInvDate) <= #10/20/2018# Then
                                If x = 1 Then
                                    BegDate = #4/19/2019#
                                    EndDate = #5/16/2019#
                                Else
                                    BegDate = #10/4/2019#
                                    EndDate = #10/31/2019#
                                End If
                    Else
                                If CDate(rst!LastInvDate) <= #11/7/2018# Then
                                    If x = 1 Then
                                        BegDate = #5/17/2019#
                                        EndDate = #6/13/2019#
                                    Else
                                        BegDate = #11/1/2019#
                                        EndDate = #11/28/2019#
                                    End If
                    Else
                            If CDate(rst!LastInvDate) <= #12/1/2018# Then
                                If x = 1 Then
                                    BegDate = #6/14/2019#
                                    EndDate = #7/11/2019#
                                Else
                                    BegDate = #11/29/2019#
                                    EndDate = #12/26/2019#
                                End If
                                End If
                            End If
                        End If
                    End If
                End If
             End If
           End If
                strSQL = "SELECT Min(Cdate(Cldr_DT)) AS NextInvDt FROM [StoreCalendarAvailability] WHERE StoreCode = " & rst![StoreCode] & " AND Cdate(Cldr_dt) >= #" & BegDate & "# AND Cdate(Cldr_dt) <= #" & EndDate & "#"
                Set rst4 = dbS.OpenRecordset(strSQL)
               
                Do Until rst4.RecordCount > 0
                    begindate = begindate - 1
                    strSQL = "SELECT Min(Cdate(Cldr_DT)) AS NextInvDt FROM [DMAvailableDates] WHERE [Dstr Mgr] = '" & rst![Dstr Mgr] & "' AND Cdate(Cldr_dt) >= #" & BegDate & "# AND Cdate(Cldr_dt) <= #" & EndDate & "#"
                    Set rst4 = dbS.OpenRecordset(strSQL)
                Loop
               
               tempInvDt = rst4!NextInvDt
                tmpTableName = "ProposedInventoryDates"
               
                If x = 1 Then
                    tmpRecName = "InvDate1"
                End If
                If x = 2 Then
                    tmpRecName = "InvDate2"
                End If
                If x = 3 Then
                    tmpRecName = "InvDate3"
                End If
                If x = 4 Then
                    tmpRecName = "InvDate4"
                End If
                DoCmd.RunSQL ("DELETE FROM " & tmpTableName & " WHERE StoreCode = " & rst!StoreCode & "")
                If rst5.RecordCount > 0 Then
                    DoCmd.RunSQL ("DELETE FROM " & tmpTableName & " WHERE StoreCode = " & rst5!Liquor & "")
                End If
                DoCmd.RunSQL ("INSERT INTO [" & tmpTableName & "] ( StoreCode, " & tmpRecName & ") VALUES(" & rst!StoreCode & ", '" & tempInvDt & "')")
                If rst5.RecordCount > 0 Then
                     DoCmd.RunSQL ("INSERT INTO [" & tmpTableName & "] ( StoreCode, " & tmpRecName & ") VALUES(" & rst5!Liquor & ", '" & tempInvDt & "')")
                End If
                DoCmd.RunSQL ("INSERT INTO [NEWInventoryList] ( StoreCode, InventoryDate ) SELECT StoreCode, " & tmpRecName & " FROM " & tmpTableName & " WHERE StoreCode = " & rst!StoreCode & " ;")
                If rst5.RecordCount > 0 Then
                    DoCmd.RunSQL ("INSERT INTO [NEWInventoryList] ( StoreCode, InventoryDate ) SELECT StoreCode, " & tmpRecName & " FROM " & tmpTableName & " WHERE StoreCode = " & Liquor & " ;")
                End If
                If numInvs = 2 Then
                    BegDate = tempInvDt + 200
                Else
                If numInvs = 3 Then
                    BegDate = tempInvDt + 100
                Else
                If numInvs = 4 Then
                    BegDate = tempInvDt + 90
                End If
                End If
                End If
                Set rst4 = dbS.OpenRecordset("SELECT fsc_qrtr_end_dt, fsc_qrtr_num FROM [user_view_cldr] WHERE Cldr_dr = #" & tempInvDt & "#;")
                If BegDate < rst4!fsc_qrtr_end_dt Then
                    BegDate = CDate(rst4!fsc_qrtr_end_dt) + 1
                End If
                x = x + 1
            Loop
        rst.MoveNext
    Loop
           
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
End Sub
Erika AndersonAccounting SystemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
That is correct.   Only a variant can hold a null.

Put a STOP at the top of the procedure, execute it, then step through it with F8 or Shift/F8 until you hit the error.

The other thing you can do is number the lines with something like MZTools, add an error handler, and in that use:

vba.erl

 and it will show you what line the error occurred on.

 also put "Option Explicit" at the top of the module and do a compile to make sure you get no errors and the code is good as it stands.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, if your indicating this line is giving you the error:

tempInvDt = rst4!NextInvDt

  Then the problem is rst4!NextInvDt is null (tempInvDt is declared as a date).

Jim.
0
Erika AndersonAccounting SystemsAuthor Commented:
Yes, I just changed from Date to Variant for tempInvDt and went to next error....is that acceptable?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Erika,

<<Yes, I just changed from Date to Variant for tempInvDt and went to next error....is that acceptable?>>

  That depends on if within the context of what your doing that makes sense; is it valid for  rst4!NextInvDt to be NULL?  and if it is, do you want to carry out the rest of the statements or not?

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.