Greetings, I am troubleshooting or in better words, needing to create an If/Then/Else Statement to handle a Null run-time error: (3075 - Syntax error in date in query expression '#'. This occurs in the code when there is not a date that matches all of the rules I have established for creating a schedule. I believe the Date variable (tempinvdt) most likely needs to be changed to variant - my question is then, do I need to change calling the variable in the syntax and remove the #" #" ?
I have tried changing the variable type from Date to Variant, but I did not change the sql statement from #' to anything and I am getting the error for Null still. Do I need to change the data type in the corresponding insert table from Date/Time to something else?
Null run-time error is one this for #" & tempInvDt & "# in the last statement below.
Dim rst5 As DAO.Recordset
Dim numInvs As Integer
Dim tempInvDt As Variant
Dim x As Integer
Dim BegDate As Date
Dim EndDate As Date
Dim tmpTableName As String
Dim tmpRecName As String
Dim strSQL As String
Dim DSTRMGR As String
Dim RegionName As String
numInvs = rst!RecFreq
DSTRMGR = rst![DSTR MGR]
RegionName = rst![Rgn Nm]
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)
tempInvDt = rst4!NextInvDt
tmpTableName = "ProposedInventoryDates"
tmpRecName = "inventorydate"
DoCmd.RunSQL ("INSERT INTO [" & tmpTableName & "] ( StoreCode, [DSTR MGR], [Rgn Nm], " & tmpRecName & ") VALUES(" & rst!StoreCode & ", '" & rst![DSTR MGR] & "', '" & rst![Rgn Nm] & "', #" & tempInvDt & "#)")
Once I am passed that, is this a proper If/Then/Else to handle where there is not a date match (null)?
If IsNull(TempInvDt) Then
Nz(rst4!tempinvdt, "No Date Found")