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 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!

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

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    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])
    )
  ),

     

    • dlc04's avatar
      dlc04
      Copper Contributor
      Apr 26, 2023

      Thank you both @NikolinoDE and Lorenzo 

       

      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!

      • dlc04's avatar
        dlc04
        Copper Contributor
        Apr 27, 2023

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

  • 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