How can I modify the SELECT statement?
 

Hi,

I'm trying to query data from MS access 97. Is there any way that I can
add the WHERE condition such that the query is executed on a certain
input date. The Access table has the start date in it, and I want the
query to run on any time frame, for example (I want to get the sum of
POVALUE from February 1, 2006 until March 3, 2006). How can I do this
or what should I modify from the code below?

Thank you so much.



Code:
--------------------
MyQuery = "SELECT SUM(JOBACTIV.POVALUE) as sum FROM JOBACTIV WHERE JOBACTIV.JOBCLASS = 'S';"
Set rs = db.OpenRecordset(MyQuery)
rs.MoveFirst
sum = rs.Fields("sum").value
Cells(1, 1).Value = sum
--------------------


--
wliong
------------------------------------------------------------------------
wliong's Profile: http://www.excelforum.com/member.php...o&userid=28343
View this thread: http://www.excelforum.com/showthread...hreadid=519883

How can I modify the SELECT statement?
 

You don't say what the name of the date column in Access is. Assume it is
StartDate.

MyQuery = "SELECT SUM(JOBACTIV.POVALUE) as sum FROM JOBACTIV WHERE
JOBACTIV.JOBCLASS = 'S' AND StartDate = #2/1/2006# and StartDate <=
#3/3/06#;"


"wliong" wrote:


Hi,

I'm trying to query data from MS access 97. Is there any way that I can
add the WHERE condition such that the query is executed on a certain
input date. The Access table has the start date in it, and I want the
query to run on any time frame, for example (I want to get the sum of
POVALUE from February 1, 2006 until March 3, 2006). How can I do this
or what should I modify from the code below?

Thank you so much.



Code:
--------------------
MyQuery = "SELECT SUM(JOBACTIV.POVALUE) as sum FROM JOBACTIV WHERE JOBACTIV.JOBCLASS = 'S';"
Set rs = db.OpenRecordset(MyQuery)
rs.MoveFirst
sum = rs.Fields("sum").value
Cells(1, 1).Value = sum
--------------------


--
wliong
------------------------------------------------------------------------
wliong's Profile: http://www.excelforum.com/member.php...o&userid=28343
View this thread: http://www.excelforum.com/showthread...hreadid=519883

How can I modify the SELECT statement?
 

It works.. Thanks.
I'm just wondering, is there any way that I could specify any date on
the start date? For example, the range of the start date is put it in
cell A1 and A2, and the query read that cell as the range for the date.

Is there any way to do that?

Thanks.


--
wliong
------------------------------------------------------------------------
wliong's Profile: http://www.excelforum.com/member.php...o&userid=28343
View this thread: http://www.excelforum.com/showthread...hreadid=519883

How can I modify the SELECT statement?
 

wliong,
Check Excel help for "parameter_query".

NickHK

"wliong" wrote in
message ...

It works.. Thanks.
I'm just wondering, is there any way that I could specify any date on
the start date? For example, the range of the start date is put it in
cell A1 and A2, and the query read that cell as the range for the date.

Is there any way to do that?

Thanks.


--
wliong
------------------------------------------------------------------------
wliong's Profile:
http://www.excelforum.com/member.php...o&userid=28343
View this thread: http://www.excelforum.com/showthread...hreadid=519883

How can I modify the SELECT statement?
 

One way:

MyQuery = _
"SELECT SUM(JOBACTIV.POVALUE) as sum " & _
"FROM JOBACTIV WHERE JOBACTIV.JOBCLASS = 'S' " & _
"AND StartDate = #" & range("A1") & "# and StartDate <= #" & _
range("A2")&"#;"


"wliong" wrote:


It works.. Thanks.
I'm just wondering, is there any way that I could specify any date on
the start date? For example, the range of the start date is put it in
cell A1 and A2, and the query read that cell as the range for the date.

Is there any way to do that?

Thanks.


--
wliong
------------------------------------------------------------------------
wliong's Profile: http://www.excelforum.com/member.php...o&userid=28343
View this thread: http://www.excelforum.com/showthread...hreadid=519883

How can I modify the SELECT statement?
 

It works...
Thanks a lot..... I really appreciate that.


--
wliong
------------------------------------------------------------------------
wliong's Profile: http://www.excelforum.com/member.php...o&userid=28343
View this thread: http://www.excelforum.com/showthread...hreadid=519883