Add inputbox value into a where clause in odbc.query

[dlc04]

Hello,

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

let
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);")
in
Source

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?

Thanks!

[#2]

@dlc04 

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:

let
    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);")
in
    Source

 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.

[#3 · Accepted solution]

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(
    DateTime.Date(
      Table.FirstValue(Excel.CurrentWorkbook(){[Name="mydate"]}[Content])
    )
  ),

[#4]

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
Details:
DataSourceKind=Odbc
DataSourcePath=dsn=Snowflake_Source
OdbcErrors=[Table]

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

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

'Refresh
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
MsgBox "Data has been refreshed!"

End Sub

Thanks again!

[#5]

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

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

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

[#6]

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