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)" |
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)" |
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)" |
All times are GMT +1. The time now is 06:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com