vba help - wild card in SQL Query

[Mallesh23]

Hi Team,

I am fetching data using in sql query
Can i use Wildcard for header. headers name might change little.


Can i use Week* for Weekly
Can i use Month* for Monthly
Can i use State* for States

how to use wild card in below query.
Sql = "Select Sum([" & hdr & "]) from [" & sht.Name & "$A1:G9] Where (Currency = ""IND"" OR Currency = ""EUR"")AND(State = """ & st & """ and City = """ & ct & """)"

Dim arr As Variant
arr = Array("Weekly", "Monthly", "1Yr", "O/W")
st = "Maharashtra"
ct = "Pune"


Sub CopyData()
Dim Conn As New ADODB.Connection
Dim Rst As 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
Dim hdr As String
'hdr = "State"
Dim cnt As Long
Dim st As String
Dim ct As String
Dim ShtName As String

Dim sh As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")

ShtName = sht.Name

Dim arr As Variant
arr = Array("Weekly", "Monthly", "1Yr", "O/W")
st = "Maharashtra"
ct = "Pune"

For I = LBound(arr) To UBound(arr)

hdr = arr(I)
MsgBox hdr

Sql = "Select Sum([" & hdr & "]) from [" & sht.Name & "$A1:G9] Where (Currency = ""IND"" OR Currency = ""EUR"")AND(State = """ & st & """ and City = """ & ct & """)"

Set Rst = New ADODB.Recordset
Rst.Open Sql, Conn
cnt = Rst.Fields(0).Value
MsgBox cnt

On Error Resume Next
Rst.Close
Set Rst = Nothing
On Error GoTo 0
Next I
End Sub

Below is a sample Table.

States	City	Weekly	Monthly	1Yr	o/w	Currency
Maharashtra	Pune	100	200	400	800	USD
Maharashtra	Pune	200	400	400	200	EUR
Maharashtra	Nagpur	300	600	600	600	IND
Maharashtra	Pune	2	3	4	5	IND
Gujarat	Surat	500	1000	1000	1000	IND
Karnataka	Bangalore	600	1200	1200	1200	IND
Delhi	New Delhi	800	1600	1600	1600	IND

Thanks
mg

 

[mrshl9898]

State like "'%" & st & "%'"

 

[Mallesh23]

Hi mrshl9898,

It worked ,Thanks for your help.



Thanks
mg