Variable passed to database query
 

We have database which start with name as clientTrans and suffixed with years
... e.g. 2000,2001,2002,2003....and for the yer it only clientTrans (without
suffixing year). So year is passed through a variable, surprissingly, the
following codes runs properly (when year is "" NULL). If value is passed to
a variable as 2006 or 2007 it does not run and flashes an error "type
mismatch".

The extract of codes are as under;




Range("J14").Select
FYEAR = ActiveCell.Value
Range("J16").Select
tyear = ActiveCell.Value
Range("K14").Select
FROMYEAR = ActiveCell.Value
Range("K16").Select
TOYEAR = ActiveCell.Value
If FYEAR = 2008 Then
DATAYEAR = ""
Else
DATAYEAR = FYEAR
End If


ODBC details....

.CommandText = Array( _
"SELECT ClientTrans" & DATAYEAR & ".ClTransClient, ClientTrans"
& DATAYEAR & ".ClTransScripName, ClientTrans" & DATAYEAR & ".ClTransDate,
ClientTrans" & DATAYEAR & ".ClTrans, ClientTrans" & DATAYEAR &
".ClTransAllot, ClientTrans" & DATAYEAR & ".ClTransRate, ClientTrans" &
DATAYEAR & ".ClTransMktRate, ClientTrans" & DATAYEAR & "" _
, _
".ClTransNetValue, ClientTrans" & DATAYEAR & ".ExchCode" &
Chr(13) & "" & Chr(10) & "FROM institution.dbo.ClientTrans" & DATAYEAR & "
ClientTrans" & DATAYEAR & "" & Chr(13) & "" & Chr(10) & "WHERE (ClientTrans"
& DATAYEAR & ".ClTransDate= " & FROMYEAR & " And ClientTrans" & DATAYEAR &
".ClTransDate<= " & TOYEAR & " ) AND (ClientTrans" & DATAYEAR &
".ClTransClient= '" & ccode & "')" & Chr(13) & "" & Chr(10) & "ORDER BY
ClientTrans" & DATAYEAR & ".ClTransDate" _
)

In short, unable to run macro with a variable passed but when variable is ""
it works. Can anybody help me to resolve this problem

Thank you in anticipation.

Variable passed to database query
 

I don't immediately see any issues with this. The string seems to evaluate
to a valid SQL statement with DATAYEAR = "" or =2008


You could vastly simplify the VBA by using a shorter table alias, like so,
where instead of "ClientTrans" & datayear as the table alias I use "CT"

dim strSQL as string

strSql = "SELECT CT.ClTransClient, CT.ClTransScripName, CT.ClTransDate, " & _
"CT.ClTrans, CT.ClTransAllot, CT.ClTransRate, CT.ClTransMktRate, " & _
"CT.ClTransNetValue, CT.ExchCode" & Chr(13) & "" & _
Chr(10) & "FROM institution.dbo.ClientTrans" & datayear & " CT " & _
"WHERE (CT.ClTransDate= " & fromYear & " And CT.ClTransDate<= " &
toYear & ") AND " & _
"(CT.ClTransClient= '" & ccode & "') " & "ORDER BY CT.ClTransDate"


..CommandText = strSQL


"Vijay Kotian" wrote:

We have database which start with name as clientTrans and suffixed with years
.. e.g. 2000,2001,2002,2003....and for the yer it only clientTrans (without
suffixing year). So year is passed through a variable, surprissingly, the
following codes runs properly (when year is "" NULL). If value is passed to
a variable as 2006 or 2007 it does not run and flashes an error "type
mismatch".

The extract of codes are as under;




Range("J14").Select
FYEAR = ActiveCell.Value
Range("J16").Select
tyear = ActiveCell.Value
Range("K14").Select
FROMYEAR = ActiveCell.Value
Range("K16").Select
TOYEAR = ActiveCell.Value
If FYEAR = 2008 Then
DATAYEAR = ""
Else
DATAYEAR = FYEAR
End If


ODBC details....

.CommandText = Array( _
"SELECT ClientTrans" & DATAYEAR & ".ClTransClient, ClientTrans"
& DATAYEAR & ".ClTransScripName, ClientTrans" & DATAYEAR & ".ClTransDate,
ClientTrans" & DATAYEAR & ".ClTrans, ClientTrans" & DATAYEAR &
".ClTransAllot, ClientTrans" & DATAYEAR & ".ClTransRate, ClientTrans" &
DATAYEAR & ".ClTransMktRate, ClientTrans" & DATAYEAR & "" _
, _
".ClTransNetValue, ClientTrans" & DATAYEAR & ".ExchCode" &
Chr(13) & "" & Chr(10) & "FROM institution.dbo.ClientTrans" & DATAYEAR & "
ClientTrans" & DATAYEAR & "" & Chr(13) & "" & Chr(10) & "WHERE (ClientTrans"
& DATAYEAR & ".ClTransDate= " & FROMYEAR & " And ClientTrans" & DATAYEAR &
".ClTransDate<= " & TOYEAR & " ) AND (ClientTrans" & DATAYEAR &
".ClTransClient= '" & ccode & "')" & Chr(13) & "" & Chr(10) & "ORDER BY
ClientTrans" & DATAYEAR & ".ClTransDate" _
)

In short, unable to run macro with a variable passed but when variable is ""
it works. Can anybody help me to resolve this problem

Thank you in anticipation.