Refreshing a query with a changing date
 

Hi,

Need help refreshing this query, but I want to to refer to a variable
date, rather than the fixed date ('2004-05-19 00:00:00').

Ideally, If I could get it to reference a cell, say cell "a1", and I
can have users put the date in there.

Regards

David

Sheets("BTCC Query").Select
Range("A1").Select
With Selection.QueryTable
..Connection = Array(Array( _
"ODBC;DSN=CCEXREPORTS;Description=SEA MSQL
d:/sea/client;UID=*********;PWD=**********;APP=Microsoft®
Query;WSID=CC-HPC-01;Network=DBMS" _
), Array("SOCN"))
..CommandText = Array( _
"SELECT cc_Handled_Calls.RDATE, cc_Handled_Calls.ARRIVALTIME,
cc_Handled_Calls.CALL_ID, cc_Handled_Calls.MEDIATYPE,
cc_Handled_Calls.QUEUE_NAME, cc_Handled_Calls.X_COL1,
cc_Handled_Calls.DURATION, cc_H" _
, _
"andled_Calls.STATE, cc_Handled_Calls.AGENT,
cc_Handled_Calls.DATE, cc_Handled_Calls.FMINPRT,
cc_Handled_Calls.MINPRT, cc_Handled_Calls.HRPRT,
cc_Handled_Calls.HRPRTX, cc_Handled_Calls.MINPRTX, cc_Hand" _
, _
"led_Calls.FMINPRTX, cc_Handled_Calls.WEEK_NO,
cc_Handled_Calls.MONTH_NO, cc_Handled_Calls.QUARTER_NO,
cc_Handled_Calls.YEAR_NO" & Chr(13) & "" & Chr(10) & "FROM
CC_HReport.dbo.cc_Handled_Calls cc_Handled_Calls" & Chr(13) & "" &
Chr(10) & "WHERE (cc_Handled" _
, "_Calls.RDATE={ts '2004-05-19 00:00:00'})" & Chr(13) & "" &
Chr(10) & "ORDER BY cc_Handled_Calls.RDATE")
..Refresh BackgroundQuery:=False
End With
ActiveWindow.LargeScroll ToRight:=0
Sheets("Front Page").Select
Range("A1").Select
End Sub


---
Message posted from http://www.ExcelForum.com/

Refreshing a query with a changing date
 

Hi,

I use this quite a lot.

The way I do it is to set a cell say A1 to a recognizable
date format and then set another one (A2) to the format
yyyy-mm-dd. I then name this cell something like DateCell
and use a macro to take the text properties of A2 and put
it into the SQL.

something like

DateToQuery = Range("Datecell").Text

........"_Calls.RDATE={ts '" & DateToQuery & " 00:00:00'})"
& Chr(13) & "" &
-----Original Message-----
Hi,

Need help refreshing this query, but I want to to refer
to a variable
date, rather than the fixed date ('2004-05-19 00:00:00').

Ideally, If I could get it to reference a cell, say
cell "a1", and I
can have users put the date in there.

Regards

David

Sheets("BTCC Query").Select
Range("A1").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=CCEXREPORTS;Description=SEA MSQL
d:/sea/client;UID=*********;PWD=**********;APP=Microsoft®
Query;WSID=CC-HPC-01;Network=DBMS" _
), Array("SOCN"))
.CommandText = Array( _
"SELECT cc_Handled_Calls.RDATE,
cc_Handled_Calls.ARRIVALTIME,
cc_Handled_Calls.CALL_ID, cc_Handled_Calls.MEDIATYPE,
cc_Handled_Calls.QUEUE_NAME, cc_Handled_Calls.X_COL1,
cc_Handled_Calls.DURATION, cc_H" _
, _
"andled_Calls.STATE, cc_Handled_Calls.AGENT,
cc_Handled_Calls.DATE, cc_Handled_Calls.FMINPRT,
cc_Handled_Calls.MINPRT, cc_Handled_Calls.HRPRT,
cc_Handled_Calls.HRPRTX, cc_Handled_Calls.MINPRTX,
cc_Hand" _
, _
"led_Calls.FMINPRTX, cc_Handled_Calls.WEEK_NO,
cc_Handled_Calls.MONTH_NO, cc_Handled_Calls.QUARTER_NO,
cc_Handled_Calls.YEAR_NO" & Chr(13) & "" & Chr(10) & "FROM
CC_HReport.dbo.cc_Handled_Calls cc_Handled_Calls" & Chr
(13) & "" &
Chr(10) & "WHERE (cc_Handled" _
, "_Calls.RDATE={ts '2004-05-19 00:00:00'})" & Chr(13)
& "" &
Chr(10) & "ORDER BY cc_Handled_Calls.RDATE")
.Refresh BackgroundQuery:=False
End With
ActiveWindow.LargeScroll ToRight:=0
Sheets("Front Page").Select
Range("A1").Select
End Sub


---
Message posted from http://www.ExcelForum.com/

.