The year is probably the wrong format try one of these changes. the date is
probably in serial date and you need to pass a string either 2002 or 02.

from
DATAYEAR = FYEAR

to
DATAYEAR = format(FYEAR,"YYYY")

or

DATAYEAR = format(FYEAR,"YY")


"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 year 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.