Add inputbox value into a where clause in odbc.query

Copper Contributor



I have an Excel with macros connected to Snowflake via ODBC and I'm trying to create an InputBox to select a date and update the where clause in the odbc.query.


What I'm trying right now is to write the value of such InputBox in cell B2 in the workbook, so that I can go to Power Query Editor and pass such date into the where clause




selectedDate = Excel.CurrentWorkbook(){[Name="mydate"]}[Content]{0}[Column1],
Source = Odbc.Query("dsn=Excel_Snowflake", "SELECT*#(lf)FROM TABLE1 A,#(lf)TABLE2 B,#(lf)TABLE3 C,#(lf)TABLE4 D#(lf)WHERE 1=1 #(lf)AND A.ID=B.ID #(lf)AND A.ID=C._ID#(lf)AND A.ID=D.ID#(lf)AND A.REPORT_DATE="& selectedDate &"#(lf)AND A.EX_IND=''#(lf)ORDER BY A.ID#(lf);")





I'm getting all sort of errors, like 

Expression.Error: We cannot apply operator & to types Text and Number.


I have tried to change the format within the code and the column, but I'm not sure if it's actually problem with the CurrentWorkbook piece?



5 Replies


The error message you're seeing suggests that the "selectedDate" variable is a number, not a text string. You can try converting the number to a text string using the Text.From function in Power Query.

Here's an updated version of your code that should work:


    selectedDate = Text.From(Excel.CurrentWorkbook(){[Name="mydate"]}[Content]{0}[Column1]),
    Source = Odbc.Query("dsn=Excel_Snowflake", "SELECT*#(lf)FROM TABLE1 A,#(lf)TABLE2 B,#(lf)TABLE3 C,#(lf)TABLE4 D#(lf)WHERE 1=1 #(lf)AND A.ID=B.ID #(lf)AND A.ID=C._ID#(lf)AND A.ID=D.ID#(lf)AND A.REPORT_DATE='" & selectedDate & "'#(lf)AND A.EX_IND=''#(lf)ORDER BY A.ID#(lf);")


 Note that the Text.From function is used to convert the selectedDate variable to a text string, and the date value is enclosed in single quotes in the ODBC query string to indicate that it's a string value.


Proposed solution with the support of AI.


best response confirmed by dlc04 (Copper Contributor)

Hi @dlc04 


Little add. to @NikolinoDE 


Assuming the value in B2 is like dd/mm/yyyy your selectedDate step returns a DateTime value (i.e. 01/01/2023 00:00:00). If SnowFlake expects a Date value (i.e. 01/01/2023) only you'll have to convert the DateTime value to a Date value before converting it to a Text value. Can be done as follow:



  selectedDate = Text.From(


Thank you both @NikolinoDE and @L z. 


Once I changed the format to text, in the Power Query Editor I'm getting the following:


DataSource.Error: ODBC: ERROR [22007] Date '4/26/2023 12:00:00 AM' is not recognized


So I tried the DateTime option since in Snowflake that is a date data type and I changed the B3 cell format to Date (MM/DD/YYYY), and it worked when I refreshed it in the Editor, but when I closed the Excel file and reopen it again, the inputbox popped out, inserted the date, and the file freezes completely and do not run.


Could it be something in the vb code about the refresh that is causing some trouble?


Here is what I have in the vb code for the inputbox and the refresh.


Private Sub Workbook_Open()
'PURPOSE: Refresh All function when file is opened

inputvalue = InputBox("Enter date(MM/DD/YYYY):")
Range("B3").Value = inputvalue

MsgBox "Data has been refreshed!"

End Sub


Thanks again!

Found a way to do it. I was using the refreshAll along with the AsyncQueriesDone


which I'm guessing it was running the query while it was trying to update such query at the same time.

So I used 

ThisWorkbook.Connections("Query - Query1").Refresh

And in the query properties I unchecked the option "Enable background refresh".

Hi @dlc04 

VBA isn't my cup to tea and the doc. of Application.CalculateUntilAsyncQueriesDone didn't help me understand what this method actually does. I was going to suggest you try disabling Background Refresh though...


Glad you found a suitable solution & Thanks for documenting it, hopefully this will help sooner or later

FYI an old case: Application.CalculateUntilAsyncQueriesDone Causes Excel to freeze and crash