Forum Discussion

dlc04's avatar
dlc04
Copper Contributor
Apr 21, 2023
Solved

Add inputbox value into a where clause in odbc.query

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 n...
BI & Data Analysis
excel
Macros and VBA
  • Lorenzo's avatar
    Lorenzo
    Apr 23, 2023

    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])
    )
  ),

     

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Apr 22, 2023

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.

 

Resources