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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,569
Office Version
2019
Platform
Windows
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.
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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]
 

rki1966

Active Member
Joined
Feb 1, 2004
Messages
351
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,139
Messages
5,466,904
Members
406,507
Latest member
donwiss

This Week's Hot Topics

Top