delete specific number records

[Jen0dorf]

Hi



I have several tables containing 1000s of records.

One field is named ChurchID_fk

THis contains a list pf number 1-85 each one designating a specific church,

I want to run a query where I can delete all records that are not equal to -say- 45. or put it another way I want to keep all records that are equal to 45

I can delete all records that have a churchID_fk of greater that 45 and then run another query of those that have a churchid_fk of less than 45 but that seems clumsy and a waste of effort.

I've looked but can't seem to get the expression correct to do this in one step.
thanks

Ian

[ranman256]

WHERE ChurchID_fk<>45

[Jen0dorf]

Hi

thanks for the reply - I was putting the figure in between the <>

I now have

DELETE tbl_Baptism.ChurchID_fk, tbl_Baptism.FicheNo, tbl_Baptism.BirthDate, tbl_Baptism.DateOfBaptism, tbl_Baptism.YearOfBaptism, tbl_Baptism.FullDateOfBaptism, tbl_Baptism.ChildsName, tbl_Baptism.Surname, tbl_Baptism.Sex, tbl_Baptism.Parents, tbl_Baptism.Abode, tbl_Baptism.Occupation, tbl_Baptism.RefNo, tbl_Baptism.PageNo, tbl_Baptism.EntryNo, tbl_Baptism.Minister, tbl_Baptism.Notes, tbl_Baptism.ForenameSDX, tbl_Baptism.SurnameSDX, "ChurchID_fk"
FROM tbl_Baptism
WHERE ((("ChurchID_fk")<>45));

But when I run it I get the error

Data Mismatch in criteria expression.


ChurchId_fk is formated number so I removed the "" ( as I thought these designated text)

but running that clears the table.

So I need help:-)

thanks

Ian

[aytee111]

Remove the quotes. ChurchID_fk is a field name on a table, select it and treat it the same as all the others.

[Jen0dorf]

Hi

I tried loosing the quotes and it deleted all the records from the table

I also tried

DELETE tbl_Baptism.ChurchID_fk, tbl_Baptism.FicheNo, tbl_Baptism.BirthDate, tbl_Baptism.DateOfBaptism, tbl_Baptism.YearOfBaptism, tbl_Baptism.FullDateOfBaptism, tbl_Baptism.ChildsName, tbl_Baptism.Surname, tbl_Baptism.Sex, tbl_Baptism.Parents, tbl_Baptism.Abode, tbl_Baptism.Occupation, tbl_Baptism.RefNo, tbl_Baptism.PageNo, tbl_Baptism.EntryNo, tbl_Baptism.Minister, tbl_Baptism.Notes, tbl_Baptism.ForenameSDX, tbl_Baptism.SurnameSDX
FROM tbl_Baptism
WHERE (((tbl_Baptism.ChurchID_fk)<>45));

but that didn't work either

thanks one and all Ian

[aytee111]

Hope you had a backup!

The statement:
DELETE * FROM tbl_Baptism WHERE ChurchID_FK<>45;
should delete all records where the fk is not equal to 45.

It doesn't make sense that your criteria is ignored, unless the data type is not a number.

[Jen0dorf]

Hi

thanks, a compact operation plus your code worked

cheers

Ian