Variant data type & handling Null with If/Then/Else

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")
Else TempInvDt
End If
Erika AndersonAccounting SystemsAsked:
Who is Participating?
 
Erika AndersonAccounting SystemsAuthor Commented:
I had to do a bit more editing on this - example where TempInvDt was attempting to add days for the next date selected, formatting: BegDate = DateAdd("d", 70, tempInvDt)

But I believe I am good to go. Thanks for looking at!
0
 
NorieVBA ExpertCommented:
Erika

Shouldn't you be doing the check for a null value before you try and run the INSERT query?
If IsNull(TempInvDt) Then
    MsgBox "No date found!"     
Else
    DoCmd.RunSQL ("INSERT INTO [" & tmpTableName & "] ( StoreCode, [DSTR MGR], [Rgn Nm],  " & tmpRecName & ") VALUES(" & 
        rst!StoreCode & ", '" & rst![DSTR MGR] & "', '" & rst![Rgn Nm] & "', #" & tempInvDt & "#)")
End If

Open in new window

0
 
Erika AndersonAccounting SystemsAuthor Commented:
Hi Norie, Yes - That is where it would appear. My question is if it is correct. I am looking to insert "No Date Found" into the temptablename, not utilize a msg box. So I am not sure if the variant would be wrapped in quotes when "No Message Box" in the If/Then and #" "# when there is a date found in the insert statement .
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Erika AndersonAccounting SystemsAuthor Commented:
I know this is probably not right, but to explain more...would it be like this:

If IsNull(tempInvDt) Then
    DoCmd.RunSQL ("Insert INTO [" & tmpTableName & "] ( StoreCode, [DSTR MGR], [Rgn Nm],  " & tmpRecName & ") " & _
"VALUES(" & rst!StoreCode & ", '" & rst![DSTR MGR] & "', '" & rst![Rgn Nm] & "', " & No Date Found & ")")

Else
   
DoCmd.RunSQL ("INSERT INTO [" & tmpTableName & "] ( StoreCode, [DSTR MGR], [Rgn Nm],  " & tmpRecName & ") " & _
"VALUES(" & rst!StoreCode & ", '" & rst![DSTR MGR] & "', '" & rst![Rgn Nm] & "', #" & tempInvDt & "#)")

End If
0
 
NorieVBA ExpertCommented:
Erika

What data type is the field 'inventorydate'?

If it's a date field it won't accept a string like 'No Date Found' and if it's a text field you would need to surround tempInvDt in quotes rather than #.
0
 
Erika AndersonAccounting SystemsAuthor Commented:
Can I pass a fake date like 01/01/1950 instead of changing the data type?
0
 
Erika AndersonAccounting SystemsAuthor Commented:
@Norie - Inventorydate was a Date/Time data type - I then switched to short text when I added the Null handling and removed the # and replaced with quotes - HOWEVER, this means I would need to change a lot more with a fiscal calendar coming from Teradata to match to quarter end dates, etc...
0
 
Erika AndersonAccounting SystemsAuthor Commented:
I am testing this now. I have change the variables that would be matching to the fiscal calendar to Variant as well...
0
 
NorieVBA ExpertCommented:
Erika

I think a 'fake' date would probably be the best solution, it would certainly make any calculations/grouping/queries easier.
0
 
Erika AndersonAccounting SystemsAuthor Commented:
What happened with changing the fiscal calendar variables to Variant was 12:00:00 AM etc was inserted into the temptable for each  location instead of 01/05/2018. I attempted to do a formatting to change it back to short date, but is not correct because I got the parameter popup...am I close or is this option a no-go?
Capture.JPG
0
 
Erika AndersonAccounting SystemsAuthor Commented:
Figured it out by changing Date to Variant, formatting
0
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.

All Courses

From novice to tech pro — start learning today.