Using for...each...next to delete certain fields

[Daniel]

I have a table with about 75 fields on it. I need to delete all fields
starting with a particular string ("OLD") as the first 3 characters.

When I use the structure

for each fld in .fields
if left(fld.name, 3)="OLD" then
.fields.delete(fld.name)
next fld

some of the fields get deleted and it appears to just skip over the
rest. If I rerun this loop, some more of the OLD fields will get
deleted and others will still be left behind.

I've noticed this also happens when deleting indexes.

Any ideas why? (please email me if possible)

Thanks!
Daniel

[Trevor Best]

Daniel wrote:

I have a table with about 75 fields on it. I need to delete all fields
starting with a particular string ("OLD") as the first 3 characters.

When I use the structure

for each fld in .fields
if left(fld.name, 3)="OLD" then
.fields.delete(fld.name)
next fld

some of the fields get deleted and it appears to just skip over the
rest. If I rerun this loop, some more of the OLD fields will get
deleted and others will still be left behind.

I've noticed this also happens when deleting indexes.

Any ideas why? (please email me if possible)

Each time you delete one, the list gets shorter by one and you skip the
next. Try looping the fields collection backwards, e.g.

For i = .fields.count-1 to 0 step -1
set fld = .fields(i)
if left(fld.name, 3)="OLD" then
.fields.delete(fld.name)
end if
set fld = nothing
next

Pity there's no "Previous" command to complment the "Next" :-)
--
This sig left intentionally blank

[Daniel]

Hey, thanks! I'll give that a try.