Update SQL

[Guest]

Hi,
I'm trying to create an SQL to update the fields based on the previous week
or field.
For example (all numeric fields), field Week1=WAU,
Week2=Week1*(PercentIncrease), Week3=Week2*(PercentIncrease), etc, to Week26.
The problem with my SQL is that it updates Week1, but not subsequent fields
Weeks 2 thru 26.
Here's what I have so:

Dim sqlc As String, i As Integer
sqlc = "UPDATE Forecast"
sqlc = sqlc & " SET Forecast.Week1 =
[Forecast].[WAU]*(1+[Forecast].[PercentIncrease]/100) "
For i = 2 To [Forms]![Main]![txtWeeks]
sqlc = sqlc & " , [Forecast].[Week" & i & "] = [Forecast].[Week" & i - 1 &
"]*(1+[Forecast].[PercentIncrease]/100)"
Next i
db.Execute sqlc, dbFailOnError

Can this even be done? Please help! thanks in advance.

 

[lucy]

Hi,
Have you tried running an execute statement for every update? there
may be a limit to how long that sql string can be. If you don't need
to run it as one big long update, run the update for each week
separately. No?
Jennifer