Set variable date range in query?
 

Hi,

At the moment I'm looking into getting data from a database into Excel. One
problem I have is entering the date range in which the data from the database
must adhere to. I do not want to have to open the query each time to change
the start and finish dates.

When I have two ranges in a sheet, called 'start' (imagine it in cell B1)
and 'finish' (placed in cell B2), how should I read 'start' and 'finish' into
the query?

Should the WHERE clause have something like:
SELECT ...
DATEADD (T.Created, INTERVAL HOUR (TIMEDIFF(TIME(NOW(), UTC_TIME())) HOUR
CREATED,
DATEADD (T.Resolved, INTERVAL HOUR (TIMEDIFF(TIME(NOW(), UTC_TIME()))
HOUR RESOLVED
WHERE "start" <= CREATED
AND CREATED <= "finish"

Note: T.Created and T.Resolved are (date) fields in the database I query.

Who knows what I need to fill in in the WHERE clause?

Abel

Set variable date range in query?
 

Dim dtStart
Dim dtEnd

dtStart = cDate(ActiveSheet.Range("B1"))
dtEnd = cDate(ActiveSheet.Range("B2"))

and use this variables in query.

If this post helps click Yes
--------------
Jacob Skaria


"Abel MacAdam" wrote:

Hi,

At the moment I'm looking into getting data from a database into Excel. One
problem I have is entering the date range in which the data from the database
must adhere to. I do not want to have to open the query each time to change
the start and finish dates.

When I have two ranges in a sheet, called 'start' (imagine it in cell B1)
and 'finish' (placed in cell B2), how should I read 'start' and 'finish' into
the query?

Should the WHERE clause have something like:
SELECT ...
DATEADD (T.Created, INTERVAL HOUR (TIMEDIFF(TIME(NOW(), UTC_TIME())) HOUR
CREATED,
DATEADD (T.Resolved, INTERVAL HOUR (TIMEDIFF(TIME(NOW(), UTC_TIME()))
HOUR RESOLVED
WHERE "start" <= CREATED
AND CREATED <= "finish"

Note: T.Created and T.Resolved are (date) fields in the database I query.

Who knows what I need to fill in in the WHERE clause?

Abel