Resetting values to zero

[pdbowling]

Hi, everyone.
This shouldn't be too hard.
I have a table with many, many columns. The columns are variable though. They could change at any time. I want to write a query that will go in to this table and reset all of the values to zero (prepping for new months data entry). I have one column that is text that holds the dept name and it shouldn't change.

something like this psuedo code

for i = 2 to fieldCount <---field 1 should be fixed
update table set f(i) = 0 <--how do I get the column name
next i

this methodology isn't important either, it's just what occured to me at first. Alternate methods would be greatly appreciated.
Thanks
PB

 

[AWithers]

Another way would be to create a table based on your current table (design only) and then insert the value in the text field(dept name), all numerical fields whould then be based on the default which you could set to 0

HTH

Andy

 

[jrbarnett]

Hi PB,

Easy:

Dim fld As Field
Dim tdf As Table
Dim i as Integer

DoCmd.SetWarnings False ' otherwise you will get lots of "Update confirm" messages
Set tdf = CurrentDb.Tables ("Tablename")

For i = 1 To (tdf.Fields.Count-1) ' indexes start from 0
	DoCmd.RunSQL "Update table Set " & tdf.Fields(i).Name & " = 0" ' replace with name of table
Next

DoCmd.SetWarnings True

You will need to add a reference to the Data Access Objects library 3.60 if you are using Access 2002 (should be fine with 97 or 2000).

John

 

[pdbowling]

Thank you, that got me on the right track.
PB