Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Statement to select a letter | Excel Worksheet Functions | |||
Trouble with select statement | Excel Programming | |||
SELECT statement | Excel Programming | |||
Adding SQL Select Statement | Excel Programming | |||
Best Way to Modify current IF() statement?????? | Excel Programming |