Utilize Dynamic Value with SQL Statement from VBA

Question

I am designing some dynamic graphs and what I would like to do is after selecting the portfolio of interest from a ComboBox it would send the name/value of the portfolio to a SQL query to collect the data. At the moment after selecting the variable the Combo Box unloads the value to cell B1.

In the graph macro I define the variable, Selected_Portfolio, as a string and initiate it per

Selected_Portfolio = Range("B1").Value

The SQL statement is then written as

sqlStr = "select * from ( "
sqlStr = sqlStr & "select Top 30 PFDate, RiskValue from dbo.Portfolios "
sqlStr = sqlStr & "where PortName = ' & Selected_Portfolio & '"  
sqlStr = sqlStr & "order by PFDate desc) sub "
sqlStr = sqlStr & "order by PFDate"

The issue I have is that I do not believe I am passing Selected_Portfolio correctly. I know this because if the hard code PortName with a specific portfolio it works.

Thanks in advance

zip · Accepted Answer · 2020-01-16 00:45:11Z

2

You are missing a couple of double quotes:

sqlStr = "select * from ( "
sqlStr = sqlStr & "select Top 30 PFDate, RiskValue from dbo.Portfolios "
sqlStr = sqlStr & "where PortName = '"  & Selected_Portfolio & "' "  
sqlStr = sqlStr & "order by PFDate desc) sub "
sqlStr = sqlStr & "order by PFDate"

answered Jan 16, 2020 at 0:45

zip

4,0612 gold badges13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Utilize Dynamic Value with SQL Statement from VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related