Help with a delete query

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

Could someone help me with a delete query? I want to build a query that
deletes the data in specific fields I have a table which has a date field
and I want to delete some of the data in some of the other fields in my
table where the date in the record is a specified date. I have tried
building a delete query but when I run it, it deletes all the data for the
specified month not just the data in the field that I want deleted. For
example say I have 10 fields called Field1, Field2,Field 3 etc and Field 1
holds the date. I want to delete the data in Fields 4, 7 and 9 where the
date in Field 1 is a date I specify in a form. I know how to reference the
data input into the form.
Thanks
Tony
 
A delete query deletes an entire record that matches a criteria.

You actually want an update query that updates fields 4,7, and 9 to ""
when the date matches.

Cheers,
Jason Lepack
 
BACKUP your data before you do this.

DELETE queries delete entire records (as you have found out)
UPDATE queries can set the value of specific fields to null (Blank).

SO you need an update query. In SQL that would look like

UPDATE [YourTable]
SET [Field4] = Null
, [Field7] = Null
, [Field9] = Null
WHERE Field1 = CDate(Forms![FormName]![ControlName])

In the query grid build a select query that shows what you want to change
Select Query: Update query from the menu
Set
Update to: Null (under each field you want to "delete"
Where : CDate(Forms![FormName]![ControlName]) under the date field.
 
You actually need to do an update query for this. Your example would
look like this:
Update MyTable
Set Field4 = Null,
Field7 = Null,
Field9 = Null
Where Field1 = Forms!MyForm!MyFieldName

Hope that helps!
 
Thanks guys you all came up with the same answer I'll have a go.
Thanks again
Tony
 
Back
Top