Database query.

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
 
J

Joel

from
"WHERE (ClientTrans.ClTransClient= ccode)" & Chr(13) & "" & Chr(10) & "ORDER
to
"WHERE (ClientTrans.ClTransClient='" & ccode & "')" & _
Chr(13) & "" & Chr(10) & "ORDER
 
V

Vijay Kotian

Compile error at

"ORDER to WHERE (ClientTrans.ClTransClient='" & ccode & "')"

Can you please help.

Thank you.
 
V

Vijay Kotian

Yeah, I got it ... initially i thought all you wrote there was VB codes
From ..... to ....

now i got it .

Thank a lot
 
J

Joel

The line continuation was missing. I like to modify the code as shown below
to make it more readable and to prevent problems when posting the code.
Basicaly you can break a string into many pieces as seen below.


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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top