Database query.

[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

 

[Joel]

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

 

[paulgu]

Assuming that the variable is ccode, the WHERE clause line should be
"WHERE (ClientTrans.ClTransClient= '" & ccode & "')" & Chr(13) & "" & Chr(10) & "ORDER ...
The variable's name is meaningless to the database handling the query - it needs the variable's value in the query.

 

[Vijay Kotian]

Compile error at

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

Can you please help.

Thank you.

 

[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

 

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

 

[paulgu]

You're going to have trouble with the line
"WHERE (ClientTrans.ClTransClient='" & ccode & ")" & _
It should be
"WHERE (ClientTrans.ClTransClient='" & ccode & "')" & _
(you are missing a single quote just before the closing bracket)
I suggest you remove all the Chr(13) & "" & Chr(10) 's as these have no effect on the result and just make it look more complex than it needs to be!