Using datetime fields as parameters in a query...

[Olaf Stetzer]

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

 

[Patrick Molloy]

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