Excel 2000 VBA recordset manipulation

[Matt.]

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

 

[Jean-Paul Viel]

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

 

[Matt.]

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.

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
(e-mail address removed)
http://www.solutionsvba.com

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