Sql Update

[Badvoc]

Im trying to update all the null values in my table (tblHours2010) to = 0
Columns in the table are week2 to week52

How would I rewrite this so I dont need to run it 54 times?

Update tblHours2010 Set week2 = 0 
Where week2 Is Null

 

[namliam]

stick this into a module and run it

sub gaanmetdiebanaan()
dim I as integer
For I = 1 to 52
currentdb.execute "Update tblHours2010 Set week" & I & " = 0  Where week" & I & " Is Null" 
next I
end sub

Happy hunting

P.S. No comment from me on bad design or dodgy solution

 

[Badvoc]

thanks for your reply but just returns:

error 3061
to few parameters, expected 1.

P.S. I didnt design the database, just have to fix it

 

[namliam]

sub gaanmetdiebanaan()
dim I as integer
For I = 1 to 52
debug.print "Update tblHours2010 Set week" & I & " = 0  Where week" & I & " Is Null" 
currentdb.execute "Update tblHours2010 Set week" & I & " = 0  Where week" & I & " Is Null" 
next I
end sub

The code is sound, it however does depend on Week1, week2, week3 etc to actually exist in your table...

If you have no week1, change "1 to 52" to "2 to 52"
If you dont have "Week" but "kwwe" or what ever change per your requirement....

 

[Badvoc]

If you have no week1, change "1 to 52" to "2 to 52" 
quote]
 
ah, that'll be the problem then, must say though, never said I had a week1;)
 
thanks all the same

 

[namliam]

Columns in the table are week2 to week52

How would I rewrite this so I dont need to run it 54 times?

2 - 52 is 51 times not 54, so I am assuming you want to run it 1-53, which would make kindoff sence as there are years that have 53 weeks.

So I used sence, sue me

 

[Badvoc]

So I used sence, sue me

Ok who are your solicitors.

 

[namliam]

Ok who are your solicitors.

Try Access, Programmers & Co