V
Vijay Kotian
In database query, the value of the variable is taken from excel file. The
variable is NON-Numeric i.e. G7220, A5E48, A856Q etc. In database query i
would like to filter out data with only required data, so i have given a
Where condition. In where condition, when i give the above codes as 'G7220'
the data is picked up by system BUT when the variable is given in place of
'G7220' the error is flashed. The variable value is shown as "G7220" where as
data can be extracted with 'G7220'. The difference is single quote and
double quote. How to solve this problem.
The codes are as under (without any variable assigned) - Data can be
extracted;
Sheets("Sheet1").Select
Range("J12").Select
ccode = ActiveCell.Value
Range("J14").Select
fyear = ActiveCell.Value
Range("J16").Select
Tyear = ActiveCell.Value
Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=10.5.2.103;UID=XXXXX;;APP=Microsoft
Office 2003;WSID=ABCDDT000053;DATABASE=YYYYYYY" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ClientTrans.ClTransClient, ClientTrans.ClTransScripName,
ClientTrans.ClTransDate, ClientTrans.ClTrans, ClientTrans.ClTransAllot,
ClientTrans.ClTransRate, ClientTrans.ClTransMktRate, ClientTrans" _
, _
".ClTransNetValue, ClientTrans.ExchCode" & Chr(13) & "" & Chr(10) &
"FROM institution.dbo.ClientTrans ClientTrans" & Chr(13) & "" & Chr(10) &
"WHERE (ClientTrans.ClTransClient= ccode)" & Chr(13) & "" & Chr(10) & "ORDER
BY ClientTrans.ClTransDate" _
)
.Name = "Query from MIS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=10.5.2.103;UID=XXXXX;;APP=Microsoft
Office 2003;WSID=ABCDDT000053;DATABASE=YYYYYYY" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ClientTrans.ClTransClient, ClientTrans.ClTransScripName,
ClientTrans.ClTransDate, ClientTrans.ClTrans, ClientTrans.ClTransAllot,
ClientTrans.ClTransRate, ClientTrans.ClTransMktRate, ClientTrans" _
, _
".ClTransNetValue, ClientTrans.ExchCode" & Chr(13) & "" & Chr(10) &
"FROM institution.dbo.ClientTrans ClientTrans" & Chr(13) & "" & Chr(10) &
"WHERE (ClientTrans.ClTransClient='KL380')" & Chr(13) & "" & Chr(10) & "ORDER
BY ClientTrans.ClTransDate" _
)
.Name = "Query from MIS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
After assinging a variable at WHERE condition data can not be pulled from
database
variable is NON-Numeric i.e. G7220, A5E48, A856Q etc. In database query i
would like to filter out data with only required data, so i have given a
Where condition. In where condition, when i give the above codes as 'G7220'
the data is picked up by system BUT when the variable is given in place of
'G7220' the error is flashed. The variable value is shown as "G7220" where as
data can be extracted with 'G7220'. The difference is single quote and
double quote. How to solve this problem.
The codes are as under (without any variable assigned) - Data can be
extracted;
Sheets("Sheet1").Select
Range("J12").Select
ccode = ActiveCell.Value
Range("J14").Select
fyear = ActiveCell.Value
Range("J16").Select
Tyear = ActiveCell.Value
Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=10.5.2.103;UID=XXXXX;;APP=Microsoft
Office 2003;WSID=ABCDDT000053;DATABASE=YYYYYYY" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ClientTrans.ClTransClient, ClientTrans.ClTransScripName,
ClientTrans.ClTransDate, ClientTrans.ClTrans, ClientTrans.ClTransAllot,
ClientTrans.ClTransRate, ClientTrans.ClTransMktRate, ClientTrans" _
, _
".ClTransNetValue, ClientTrans.ExchCode" & Chr(13) & "" & Chr(10) &
"FROM institution.dbo.ClientTrans ClientTrans" & Chr(13) & "" & Chr(10) &
"WHERE (ClientTrans.ClTransClient= ccode)" & Chr(13) & "" & Chr(10) & "ORDER
BY ClientTrans.ClTransDate" _
)
.Name = "Query from MIS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=10.5.2.103;UID=XXXXX;;APP=Microsoft
Office 2003;WSID=ABCDDT000053;DATABASE=YYYYYYY" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ClientTrans.ClTransClient, ClientTrans.ClTransScripName,
ClientTrans.ClTransDate, ClientTrans.ClTrans, ClientTrans.ClTransAllot,
ClientTrans.ClTransRate, ClientTrans.ClTransMktRate, ClientTrans" _
, _
".ClTransNetValue, ClientTrans.ExchCode" & Chr(13) & "" & Chr(10) &
"FROM institution.dbo.ClientTrans ClientTrans" & Chr(13) & "" & Chr(10) &
"WHERE (ClientTrans.ClTransClient='KL380')" & Chr(13) & "" & Chr(10) & "ORDER
BY ClientTrans.ClTransDate" _
)
.Name = "Query from MIS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
After assinging a variable at WHERE condition data can not be pulled from
database