using Query Database Query in Excel 2003

[vranjit138]

Hi,

Iam trying to use a variable, so that the user can select a particular text and use it in the database query

But i find that string b is not being taken up by the code -- (((data1.Gender=", "'b'))")

Can anyone tell me how to pass a variable to this code of Database query in excel

Dim a As String
Dim b As String
a = Sheets("Sheet2").Range("A4").Text
b = Sheets("Sheet2").Range("b4").Text
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Excel Files;DBQ=C:\REPORTS\Team1.xls;DefaultDir=C:\REPORTS;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("F4"))
.CommandText = Array( _
"SELECT data1.Name, data1.Gender, Lotus.`Lotus Notes Long Name`" & Chr(13) & "" & Chr(10) & "FROM `C:\REPORTS\Team1`.data1 data1, `C:\REPORTS\Team2`.Lotus Lotus" & Chr(13) & "" & Chr(10) & "WHERE data1.`Employee ID` = Lotus.`Employee ID` AND ((data1.Gender=", "'b'))")
.Name = "Query from Excel Files"

 

[DonkeyOte]

Perhaps you mean:

"SELECT data1.Name, data1.Gender, Lotus.`Lotus Notes Long Name` FROM `C:\REPORTS\Team1`.data1 data1, `C:\REPORTS\Team2`.Lotus Lotus WHERE data1.`Employee ID` = Lotus.`Employee ID` AND data1.Gender = '" & b & "'"

we assume Gender field is of String Type.