Database query.
- From: Vijay Kotian <VijayKotian@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 28 Aug 2008 01:28:01 -0700
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
.
- Follow-Ups:
- RE: Database query.
- From: Joel
- RE: Database query.
- Prev by Date: Copying columns (but cells should be copied in different order) between two files or worksheets
- Next by Date: How to run a video clip through macro.
- Previous by thread: Copying columns (but cells should be copied in different order) between two files or worksheets
- Next by thread: RE: Database query.
- Index(es):
Relevant Pages
|
