Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA recordset manipulation
Hi all!
I hope this one is pretty simple. I have created a recordset as defined by the code below. How do I populate the appended field with the WeekNumber function as indicated by the last line of the code? Thanks in advance! cheers, Matt. ---------------- strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity " _ & "FROM qryWeeklyStartupScrap " _ & "WHERE [Machine #] <= 14 " _ & "GROUP BY ProDate" rsWeeklyScrap.CursorType = adOpenDynamic rsWeeklyScrap.CursorLocation = adUseClient rsWeeklyScrap.Source = strSQL rsWeeklyScrap.ActiveConnection = connDB rsWeeklyScrap.Open rsWeeklyScrap.Fields.Append "WeekNum", adInteger strSQL = "UPDATE rsWeeklyScrap SET WeekNum = WeekNumber(ProDate)" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA recordset manipulation
Hi
I think that the best way will be to do that in your source query like that: strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity " _ & " , format([Prodate],"ww") as theMonth FROM qryWeeklyStartupScrap -- JP http://www.solutionsvba.com "Matt." wrote in message .. . Hi all! I hope this one is pretty simple. I have created a recordset as defined by the code below. How do I populate the appended field with the WeekNumber function as indicated by the last line of the code? Thanks in advance! cheers, Matt. ---------------- strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity " _ & "FROM qryWeeklyStartupScrap " _ & "WHERE [Machine #] <= 14 " _ & "GROUP BY ProDate" rsWeeklyScrap.CursorType = adOpenDynamic rsWeeklyScrap.CursorLocation = adUseClient rsWeeklyScrap.Source = strSQL rsWeeklyScrap.ActiveConnection = connDB rsWeeklyScrap.Open rsWeeklyScrap.Fields.Append "WeekNum", adInteger strSQL = "UPDATE rsWeeklyScrap SET WeekNum = WeekNumber(ProDate)" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA recordset manipulation
Thanks Jean Paul
According to MS's website, there is a bug in the format command that will calculate a week 53, and that is unacceptable. MS has published a routine that will properly calculate the weeknumber. However, I can't trigger the function in Access from Excel, and I can't pass use the function in Excel to affect data in Access using VBA. Thus I need to manipulate the recordset after the records are retrieved. cheers, Matt. "Jean-Paul Viel" wrote in message .. . Hi I think that the best way will be to do that in your source query like that: strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity " _ & " , format([Prodate],"ww") as theMonth FROM qryWeeklyStartupScrap -- JP http://www.solutionsvba.com "Matt." wrote in message .. . Hi all! I hope this one is pretty simple. I have created a recordset as defined by the code below. How do I populate the appended field with the WeekNumber function as indicated by the last line of the code? Thanks in advance! cheers, Matt. ---------------- strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity " _ & "FROM qryWeeklyStartupScrap " _ & "WHERE [Machine #] <= 14 " _ & "GROUP BY ProDate" rsWeeklyScrap.CursorType = adOpenDynamic rsWeeklyScrap.CursorLocation = adUseClient rsWeeklyScrap.Source = strSQL rsWeeklyScrap.ActiveConnection = connDB rsWeeklyScrap.Open rsWeeklyScrap.Fields.Append "WeekNum", adInteger strSQL = "UPDATE rsWeeklyScrap SET WeekNum = WeekNumber(ProDate)" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Manipulation | Excel Discussion (Misc queries) | |||
Data Manipulation - Excel 2000 | Excel Discussion (Misc queries) | |||
Excel Time Manipulation | Excel Discussion (Misc queries) | |||
Recordset Opening Excel File | Excel Programming | |||
open range (within workbook) as ado recordset - excel vba | Excel Programming |