Forum Discussion

Michelle99's avatar
Michelle99
Brass Contributor
Sep 08, 2020
Solved

ODBC data connection with parameter from drop down

Hi there,   Via ODBC I am connecting to a Pervasive (version 13) database. Is there a way for me to somehow make use of a parameter? For example, in the below screenshot I've shown (although it is ...
BI & Data Analysis
excel
  • Michelle99's avatar
    Michelle99
    Sep 10, 2020

    SergeiBaklan thank you that is exactly it! I added the line you mentioned and it works! For anyone else looking for this solution, here it is:

     

     

    let
    pperiod = Excel.CurrentWorkbook(){[Name="reneirs_pperiod"]}[Content]{0}[Column1],
    pperiod_text = Text.From(pperiod),
    Source = Odbc.Query("dsn=PASTEL.odbc", "select * from LedgerTransactions where PPeriod = " & pperiod_text)
in
    Source

     

     

Michelle99's avatar
Michelle99
Brass Contributor
Sep 10, 2020

SergeiBaklan thank you that is exactly it! I added the line you mentioned and it works! For anyone else looking for this solution, here it is:

 

 

let
    pperiod = Excel.CurrentWorkbook(){[Name="reneirs_pperiod"]}[Content]{0}[Column1],
    pperiod_text = Text.From(pperiod),
    Source = Odbc.Query("dsn=PASTEL.odbc", "select * from LedgerTransactions where PPeriod = " & pperiod_text)
in
    Source

 

 

Michelle99's avatar
Michelle99
Brass Contributor
Sep 10, 2020

I have one Laaaaast Question SergeiBaklan ... Every time I select a new PPeriod in my yellow dropdown, I have to go and refresh my query for the new data to be reflected in the table. Question: Am I correct that I'll need to write some VBA code to "force" automatic query refresh every time my user selects a new PPeriod?

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 04, 2021

    MaxMilanka , glad to know you solved an issue, thank you for feedback

  • MaxMilanka's avatar
    MaxMilanka
    Copper Contributor
    Dec 03, 2021

    SergeiBaklan, Thank you so much. Found another way of doing it. Really appreciate your feedback.
    https://www.youtube.com/watch?v=OTBBX485Fg8

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 03, 2021

    MaxMilanka 

    I guess it shall be

    Booking = Excel.CurrentWorkbook(){[Name="booking"]}[Content]{0}[Column1]
  • MaxMilanka's avatar
    MaxMilanka
    Copper Contributor
    Dec 03, 2021

    SergeiBaklan Michelle99 This has been very useful so far. H'ever, I'm having an error like in the screenshot and will you able to help? Unable to convert to text as well. Appreciate your help.

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 10, 2020

    Michelle99 

    Great to know it finally works, thanks for the result sharing.

     

    Yes, if automatic upon parameter is changed that's VBA.  Can't advise with concrete code, but you may google for the pattern or ask as separate question this forum people.

Resources