Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using datetime fields as parameters in a query...
Hello,
I am trying to set up an excel sheet which queries data from several mysql tables (via myODBC). In short, the first query results in two datetime fields and some other information. Now I want to use these two datetime fields as parameters in a second query but this seems not to work. The second qery looks like SELECT something FROM tableXYZ WHERE datetimefield BETWEEN [t_low] AND [t_high] I edited this query with the MS-Query-editor. In excel I assinged the correct cells (result from first query) to the parameters t_low and t_high but I always get an empty result from this. I have the feeling that the problem lies in the type conversion between the excel cell and a datetime field for SQL. If I try to edit the parameters in QUERY I get a box where one chooses a datatype normally. I cannot change this from varchar (greyed out, not changeable). I am really stuck here, can anyone help me, please? Thanks, Olaf |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using datetime fields as parameters in a query...
DIM DateHigh as string,DateLow as string
DIM sql as string DateHigh=FORMAT(Range("A1"),"dd-mmm-yyyy") DateLow =FORMAT(Range("A2"),"dd-mmm-yyyy") sql = "SELECT something FROM tableXYZ where " sql = sql & "datetimefield '" & DateLow "'" sql = sql & " AND datetimefield <'" & DateDateHigh "'" this would give ... SELECT something FROM tableXYZ where datetimefield '15-Dec-2003' AND datetimefield < '6-Jan-2004' (one line!) note the date format and in SINGLE quoe marks Patrick Molloy Microsoft Excel MVP -----Original Message----- Hello, I am trying to set up an excel sheet which queries data from several mysql tables (via myODBC). In short, the first query results in two datetime fields and some other information. Now I want to use these two datetime fields as parameters in a second query but this seems not to work. The second qery looks like SELECT something FROM tableXYZ WHERE datetimefield BETWEEN [t_low] AND [t_high] I edited this query with the MS-Query-editor. In excel I assinged the correct cells (result from first query) to the parameters t_low and t_high but I always get an empty result from this. I have the feeling that the problem lies in the type conversion between the excel cell and a datetime field for SQL. If I try to edit the parameters in QUERY I get a box where one chooses a datatype normally. I cannot change this from varchar (greyed out, not changeable). I am really stuck here, can anyone help me, please? Thanks, Olaf . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Query - 2 Parameters | Excel Discussion (Misc queries) | |||
MS Query DateTime String | Excel Worksheet Functions | |||
Query Parameters | Excel Discussion (Misc queries) | |||
Query Parameters | Excel Programming | |||
datetime in query | Excel Programming |