Deleting data in fields for a table.

[Guest]

I need to delete all data in all the field for a record in a table. Except I
need the first field left intact. I know I can ref the fields by name but is
there another way to ref the fields. I would like to find the record and
then loop thru the fields. Field(1)...Field(n)

Mike J

 

[John Vinson]

I need to delete all data in all the field for a record in a table. Except I
need the first field left intact. I know I can ref the fields by name but is
there another way to ref the fields. I would like to find the record and
then loop thru the fields. Field(1)...Field(n)

Mike J

It's not necessary to loop. An Update query will do the trick; set
each field to NULL.

UPDATE yourtable
SET Field2=Null, Field3=Null, Field4=Null, ..., Fieldn=Null
WHERE Field1 = [Enter record to delete:]

using your actual fieldnames.

John W. Vinson[MVP]

 

[Guest]

Hey'
Thanks. I thought of the same thing when I was going to sleep last night
but was too tired to get up. Thx.
However, the 'null doesn't work with a date field and a phone# fiel both
fields having a mask. Is there a workarounnd for this?

Mike J

I need to delete all data in all the field for a record in a table. Except I
need the first field left intact. I know I can ref the fields by name but is
there another way to ref the fields. I would like to find the record and
then loop thru the fields. Field(1)...Field(n)

Mike J

It's not necessary to loop. An Update query will do the trick; set
each field to NULL.

UPDATE yourtable
SET Field2=Null, Field3=Null, Field4=Null, ..., Fieldn=Null
WHERE Field1 = [Enter record to delete:]

using your actual fieldnames.

John W. Vinson[MVP]

 

[John Vinson]

However, the 'null doesn't work with a date field and a phone# fiel both
fields having a mask. Is there a workarounnd for this?

Null should work fine for date fields and for masks. You're not
updating to the text string "Null" are you? The proper syntax is just
to update to Null. Perhaps you could post the SQL of your update query
if this isn't making sense.

Note a couple of things:

- You will not be able to update any Required field to NULL - that's
what Required means, that Null is not a valid option
- The need to do this in the first place (blank out all fields for a
given record) is quite unusual; any reason not to just delete the
record?

John W. Vinson[MVP]