Variable passed to database query

[Vijay Kotian]

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.

 

[Duke Carey]

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