sql editor mysql parameter issue

[Anonymous]

Hello,

I want to use a parameter in my mysql source query. I've created a parameter (Power query Editor) "start" (in my tests it doesn't matter if type is date or text) and trying to retrieve some records. In my mysql query I used: DATE(voorstelling.`aanvangstijd`) >= '" & start & "' ( start has a value of 2022-02-28).

It does show me all the records though and not only the records greater or equal then 2022-02-28. After al lot of testing I opend the advanced editor and it showed me the following for the parameter: DATE(voorstelling.`aanvangstijd`) >= '"" & start & ""'

Notice there is twice a double quote on both sides, I changed this to:  >= '" & start & "' and suddenly all works well.

However, by doing this I'm not able to edit the mysql query anymore, the icon to edit the query has gone.

before:

 

after:

 

Is this a bug? How can I correct the mysql query so all works well? Ive tested with different variations of quotes to no avail.

Thnx Remco

[bcdobbs · Accepted solution]

Hi @Anonymous,

Not a bug as such. It's Power

Query saying "you've changed something in advanced editor so you can't use the wizard for this step any more".

Is voorstelling.`aanvangstijd` a date type column in sql?

You might also find this useful, from

Chris Webb which is a much nicer way to pass parameters instead of string manipulation: https://blog.crossjoin.co.uk/2016/12/11/passing-parameters-to-sql-queries-with-value-nativequery-in-...