Use Cell value in Sql Query

rki1966

Active Member
Joined
Feb 1, 2004
Messages
351
I am trying to use a cell value(Parameter), the same as I use with Microsoft query, but it is not working.

Example: Select * from table where Date = ?

Using Power Query

Defined the Criteria

let
Source = Excel.CurrentWorkbook(){[Name="AsOfDateCriteria"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"AsOfDate", type date}}),
ANDCriteria01 = Record.Field(#"Changed Type"{0},"AsOfDate")
in
ANDCriteria01



Sql Query

let
ANDCriteria01 = AsOfDateCriteria,
Source = Sql.Database("plrs-sql", "polaris", [Query="SELECT * FROM Polaris.dbo.MyTable Where AsOFDate = AsOfDateCriteria"])
in
Source


Error
Message=Invalid column name 'AsOfDateCriteria'.

The first step shows the value of 04/11/2019.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try this:

Add a column and then create an if statement that looks in a particular column for the date and the else statement is " ". You can then filter on the new column to only show the non blanks.
 
Upvote 0
I think you need to build the queryt string, like so

Code:
[LEFT][COLOR=#333333][FONT=Verdana][Query="SELECT * FROM Polaris.dbo.MyTable Where AsOFDate = '" & AsOfDateCriteria & "'"][/FONT][/COLOR][/LEFT]

and the date might be datetime and not text so maybe even

Code:
[/FONT][/COLOR][COLOR=#333333][FONT=Verdana][Query="SELECT * FROM Polaris.dbo.MyTable Where AsOFDate = [LEFT][COLOR=#222222][FONT=Verdana]'" & Date.ToText(DateTime.Date(myDate)) & "'"][/FONT][/COLOR][/LEFT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana]
 
Upvote 0
Thanks,
I made the change and got this error. I copied the data from the first query and pasted to the Second query and it worked.

Formula.Firewall: Query 'Holdings' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top