Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable passed to database query
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable passed to database query
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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable passed to database query
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," & _ "Vijay Kotian" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable passed to database query | Excel Discussion (Misc queries) | |||
For Next Variable Not Being Passed Through For Second Sheet | Excel Programming | |||
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) | |||
Modify range variable passed through Function | Excel Programming |