query with a date parameter

[mahmud1180]

The following query works in Acess Vba.

Query2 = "SELECT Futures.Date, Futures.PX_OPEN, Futures.PX_HIGH, Futures.PX_LOW, Futures.PX_SETTLE FROM Futures WHERE (((Futures.Date) Between #12/31/2013# And #2/28/2014#));"


But If I get the value of date from excel it gives an error. I have tried putting it like that:

Dim D1 As Date
Dim D2 As Date



D1 = y.Range("A1").Value
D2 = y.Range("A2").Value

Query2 = "SELECT Futures.Date, Futures.PX_OPEN, Futures.PX_HIGH, Futures.PX_LOW, Futures.PX_SETTLE FROM Futures WHERE (((Futures.Date) Between #D1# And #D2#));"

This code gives me an error. I also tried using date number; still get the same error. Any suggestion?



Thanks

[ranman256]

You have to separate the variable from the string:

Query2 = "SELECT Futures.Date, Futures.PX_OPEN, Futures.PX_HIGH, Futures.PX_LOW, Futures.PX_SETTLE FROM Futures WHERE (((Futures.Date) Between #" & D1 & "# And #" & D2 & "#));"

[JoeM]

If you want to pass in the value of the variables, then cannot be placed inside the literal text, or else they are treated as literal text (literally "D1" and "D2").

Try:

Query2 = "SELECT Futures.Date, Futures.PX_OPEN, Futures.PX_HIGH, Futures.PX_LOW, Futures.PX_SETTLE FROM Futures WHERE (((Futures.Date) Between #" & D1 & "# And #" & D2 & "#));"

[mahmud1180]

Thank you guys. It worked