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.

Please Login or Register  to view this content.

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
>
>

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,
Check Excel help for "parameter_query".

NickHK

"wliong" <wliong.24blrb_1141770002.0149@excelforum-nospam.com> wrote in
message news:wliong.24blrb_1141770002.0149@excelforum-nospam.com...
>
> 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
>

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
>
>

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