asked on

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

Jim Dettman (EE MVE)

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.

Jim Dettman (EE MVE)

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.

Erika Anderson

ASKER

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

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial