Sql Query Problem

[mxnmx]

Hey Again
I have this query now:

Delete * FROM final where Vaccination <> 'BCG' or Vaccination <> 'BCG & POLIO' or Vaccination <> 'Booster DPT & Polio' or Vaccination <> 'Booster DT & Polio' or vaccination<>'Booster Hepatitis B' or vaccination <>'CHICKENPOX VACCINE' or vaccination <>'DPT' or vaccination<>'DPT 1 & POLIO' or vaccination<>'DPT 2 & POLIO' or vaccination<>'DPT 3 & POLIO' or vaccination<>'DT & POLIO 1' or vaccination<>'DT & POLIO 2' or or vaccination<>'DT & POLIO 3' or vaccination<>'H.I.B.1' or vaccination<>'H.I.B.2' or vaccination<>'H.I.B.3' or vaccination <>'H.I.B.Booster' or vaccination<>'Hepatitis A 1' or vaccination<>'Hepatitis A 2' or vaccination<>'Hepatitis A 3' or vaccination<>'Hepatitis B 1' or vaccination<>'Hepatitis B 2' or vaccination<>'Hepatitis B 3' or vaccination<> 'Hepatitis B Booster' or vaccination<>'M.M.R.' or vaccination<>'Measles' or vaccination<>'Meningococal A+C' or vaccination<>'Polio' or vaccination<>'Rabies Vaccine' or vaccination<>'T.T' or vaccination<>'Typhi' or vaccination <>'Yellow Fever'

When I run this query it deletes everything form the field Vaccination.

[randem]

mxnmx,

Do you mean it deletes everything form the table Final, It can not possibly delete information from fields, only Update queries can do that.

If it deletes all records try using and instead of or.

[mxnmx]

No it does not delete everything form the Table but everything in the field Vaccination.

[randem]

mxnmx,

What database are you using.


A delete query can not and will not delete information from fields. Delete is a row operator not a column operator.

[Manny Somarriba]

What about if you change the comparison <> operator. Do you want to omit all these fields. Also you can use words "is not iqual" please organize your query fields

[mxnmx]

I'm using Access XP.

[mxnmx]

Thanks randem
I exactly know what you are saying and thats what I expected and thats why I posted it here. My Table has about 42,000 records. So I dont really know why is it happening...

[randem]

mxnmx,

If you can convert the database to a prior version (Access 2000), I can take a look. It might be Access XP or something on your PC. But there is no way that what you described supposed to happen.

[mxnmx]

Ok I have attached the database along with the 2 queries and that table. Please have a look

[randem]

mxnmx,


I modified the db you posted to do exactly what you wanted. Your perception of what was happening was the issue. The query was not deleting the information in the fields, it was deleting all the records and leaving the ones that had nothing in the field Vaccinations.

I changed your or's to and's and this works fine, except is does not do exactly what you want so I modified your other query to give you exactly what you wanted.


See Attached

[randem]

mxnmx,

Sorry that post got away!


DELETE FINAL.VACCINATION, *
FROM FINAL
WHERE (((FINAL.VACCINATION) Is Null));

Is what you are looking for.

[mxnmx]

Thanks so much randem....that rocks!!!