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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

NorieAnalyst Assistant Commented:
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
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
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
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:
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

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
Erika AndersonAccounting SystemsAuthor Commented:
Figured it out by changing Date to Variant, formatting
0
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
Query Syntax

From novice to tech pro — start learning today.