vba help - how to replace string with variable in a query.

[Mallesh23]

Hi Team,

how to pass variable in a sql Query string.
I want to replace ""USD"" with variable str_currency

working.....
Sql = "Select Sum(Weekly) from [Sheet1$] Where (Currency = ""USD"")" ' This code works

Not working
Sql = "Select Sum(Weekly) from [Sheet1$] Where (Currency = " & str_currency & ")" 'This is not working how to pass variable


Below code which I was trying.

Sub CopyData()
    Dim Conn As New ADODB.Connection
    Dim Rst As New ADODB.Recordset
    
    FilePath = ThisWorkbook.FullName
    connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath & _
                    ";Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
    
    Conn.Open connstr
   ' Sql = "Select * from [Sheet1$] order by State Desc"
   'Sql = "Select State,city from [Sheet1$] "
  'Sql = "SELECT Sum(city)FROM [Sheet1$] where (State = ""Gujarat"")"



Dim str_currency As String
str_currency = USD

Sql = "Select Sum(Weekly) from [Sheet1$] Where (Currency = ""USD"")"  ' This code works
Sql = "Select Sum(Weekly) from [Sheet1$] Where (Currency = " & str_currency & " )" 'This is not working how to pass variable

    
    Rst.Open Sql, Conn
    Dim cnt As Double
    
   cnt = Rst.Fields(0).Value
   MsgBox cnt
   
End Sub

Thanks
mg

 

[RoryA]

You're missing the quotes:

Sql = "Select Sum(Weekly) from [Sheet1$] Where (Currency = """ & str_currency & """)"

though I think most languages would allow a single quote:

Sql = "Select Sum(Weekly) from [Sheet1$] Where (Currency = '" & str_currency & "')"

 

[Mallesh23]

Hi Rory,

Thanks a lot both the solution worked. ?


Thanks
mg