Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
database query not showing foxpro database How I import data | New Users to Excel | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |