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

 

[Joel]

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]

I tried to run macro after making necessary changes infomed by you. I still
get type mismatch for both formats e.g. "YYYY" and "YY". After these chagnes
the value in the variable (datayear) is shown as "1905" ?

Your another point as making them String - Since datayear is showing
"1905" I made changes in the code so that datayear can appear as string i.e.
dim datayear as string OR datayear = format(FYEAR, Text). Inspite of doing
all these changes the error is still flashed as "Type mismatch" and unable to
download ?

 

[Joel]

I went back and looked and some of your previous postings I found the code
below. I hope this make it easier to understand how to handle database query
strings.

"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

------------------------------------------------------------------------------------------

Notice this section : "WHERE (ClientTrans.ClTransClient='KL380')"

This is the database variable : ClientTrans.ClTransClient. The database
table is ClientTrans and the column from that table is : ClTransClient. You
are removing clients with parameter 'KL380' with single quotes around the
parameter.

Now lets look at the date

from
"SELECT ClientTrans.ClTransClient, ClientTrans.ClTransScripName,
ClientTrans.ClTransDate, ClientTrans.ClTrans, ClientTrans.ClTransAllot,
ClientTrans.ClTransRate, ClientTrans.ClTransMktRate, ClientTrans"

to
"SELECT ClientTrans.ClTransClient, ClientTrans.ClTransScripName," & _
"ClientTrans.ClTransDate, ClientTrans.ClTrans, ClientTrans.ClTransAllot," & _
"ClientTrans.ClTransRate, ClientTrans.ClTransMktRate, ClientTrans"

The 1st step I did was just to add line continuation characters breaking 1
string into 3 pieces. The code is exactly the same as the original.

"SELECT ClientTrans.ClTransClient, ClientTrans.ClTransScripName," & _
"ClientTrans.ClTransDate" & _
", ClientTrans.ClTrans, ClientTrans.ClTransAllot," & _
"ClientTrans.ClTransRate, ClientTrans.ClTransMktRate, ClientTrans"

Now I made the date a seperate string and added another line.

"SELECT ClientTrans.ClTransClient, ClientTrans.ClTransScripName," & _
"ClientTrans.ClTransDate='" & DateValue & _
"', ClientTrans.ClTrans, ClientTrans.ClTransAllot," & _
"ClientTrans.ClTransRate, ClientTrans.ClTransMktRate, ClientTrans"

Now I added the equal sign and put in the single quotes. Notice there are 3
tickmarks after the equal sign and at the beginning of the 3rd line.

To make it easier to see I'm adding some extra spaces

ClTransDate= ' "

" ' , ClientTrans.ClTrans, ClientTrans.ClTransAllot," & _