update query

[davanderbilt]

I have an Access update query that is not working.

Here's my SQL:
UPDATE tablename SET tablename.HeartAttackDOD = " "
WHERE(((tablename.HeartAttackDOD)=#1/1/2001#) AND ((tablename.HeartAttack)="2"));

It sets HeartAttackDOD = " " even if HeartAttack = "1" and I only want it to set HeartAttackDOD = " " if HeartAttack = "2".

Can anyone help?

 

[randy700]

Unless your HeartAttack field is a string, remove the quotes from around the number 2.

WHERE tablename.HeartAttackDOD = #1/1/2001# AND tablename.HeartAttack = 2

Randy

 

[davanderbilt]

Thanks, I removed the quotes from around the 2 since it isn't a string value, but it is still setting HeartAttackDOD = " " when there is a value of 1 in HeartAttack.

UPDATE tablename SET tablename.HeartAttackDOD = " "
WHERE(((tablename.HeartAttackDOD)=#1/1/2001#) AND ((tablename.HeartAttack)=2));

 

[randy700]

When I tried to duplicate your results, I got a type conversion error and nothing gets updated. However, if I change the " " to null, it works.

UPDATE tablename SET tablename.HeartAttackDOD = [COLOR=red]NULL[/color] WHERE(((tablename.HeartAttackDOD)=#1/1/2001#) AND ((tablename.HeartAttack)=2));

Randy

 

[davanderbilt]

When I tried that, I got a data type mismatch error.

 

[davanderbilt]

Randy,
Please ignore my last post. I fixed the data type mismatch problem.

I tried setting to NULL instead of " ", but it is setting HeartAttackDOD = NULL even when there is a value of 1 in HeartAttack.

This is a problem because a value of 1 means that the person did have a heart attack and we need the HeartAttackDOD (date of diagnosis). When I run the update query it is wiping out the HeartAttackDOD even if I have another date in there other than 01/01/2001 (02/02/2007 for example).

 

[davanderbilt]

Here's the entire update query:

UPDATE Health_History SET Health_History.HeartAttackDOD = Null, Health_History.AnginaDOD = Null, Health_History.IrregularHeartDOD = Null, Health_History.OtherHeartDOD = Null, Health_History.StrokeDOD = Null, Health_History.FainitingDOD = Null, Health_History.HBPDOD = Null, Health_History.HighCholDOD = Null, Health_History.ThyroidDOD = Null, Health_History.CancerDOD = Null, Health_History.KidneyDOD = Null, Health_History.LiverDOD = Null, Health_History.GoutDOD = Null, Health_History.DiabetesDOD = Null, Health_History.PsychDOD = Null, Health_History.DrugDOD = Null, Health_History.EatingDOD = Null, Health_History.Other1DOD = Null, Health_History.LastMenstrualPeriod = Null
WHERE (((Health_History.HeartAttackDOD)=#2/29/2000#) AND ((Health_History.HeartAttack)=2)) OR (((Health_History.AnginaDOD)=#2/29/2000#) AND ((Health_History.Angina)=2)) OR (((Health_History.IrregularHeartDOD)=#2/29/2000#) AND ((Health_History.IrregularHeart)=2)) OR (((Health_History.OtherHeartDOD)=#2/29/2000#) AND ((Health_History.OtherHeart)=2)) OR (((Health_History.StrokeDOD)=#2/29/2000#) AND ((Health_History.Stroke)=2)) OR (((Health_History.FainitingDOD)=#2/29/2000#) AND ((Health_History.Fainting)=2)) OR (((Health_History.HBPDOD)=#2/29/2000#) AND ((Health_History.HBP)=2)) OR (((Health_History.HighCholDOD)=#2/29/2000#) AND ((Health_History.HighChol)=2)) OR (((Health_History.ThyroidDOD)=#2/29/2000#) AND ((Health_History.Thyroid)=2)) OR (((Health_History.CancerDOD)=#2/29/2000#) AND ((Health_History.Cancer)=2)) OR (((Health_History.KidneyDOD)=#2/29/2000#) AND ((Health_History.Kidney)=2)) OR (((Health_History.LiverDOD)=#2/29/2000#) AND ((Health_History.Liver)=2)) OR (((Health_History.GoutDOD)=#2/29/2000#) AND ((Health_History.Gout)=2)) OR (((Health_History.DiabetesDOD)=#2/29/2000#) AND ((Health_History.Diabetes)=2)) OR (((Health_History.PsychDOD)=#2/29/2000#) AND ((Health_History.Psych)=2)) OR (((Health_History.DrugDOD)=#2/29/2000#) AND ((Health_History.Drug)=2)) OR (((Health_History.EatingDOD)=#2/29/2000#) AND ((Health_History.Eating)=2)) OR (((Health_History.Other1DOD)=#2/29/2000#) AND ((Health_History.Other1)=2)) OR (((Health_History.LastMenstrualPeriod)=#2/29/2000#) AND ((Health_History.Gender)=1));

 

[PHV]

I'm afraid you have to write 19 update queries ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davanderbilt]

If I do a separate update query for every value (i.e. one for HeartAttackDOD, one for AnginaDOD, one for IrregularHeartDOD, and so on) it works as it should, but when they are all combined into one update query is when it doesn't work.

 

[davanderbilt]

I was afraid of that. Thanks for your help.

 

[PHV]

A starting point for an All-in-one query:
UPDATE Health_History
SET HeartAttackDOD = IIf(HeartAttackDOD=#2/29/2000# AND HeartAttack=2,Null,HeartAttackDOD)
, AnginaDOD = IIf(AnginaDOD=#2/29/2000# AND Angina=2,Null,AnginaDOD)
, ...
, LastMenstrualPeriod = IIf(LastMenstrualPeriod=#2/29/2000# AND Gender=1,Null,LastMenstrualPeriod)
WHERE (((Health_History.HeartAttackDOD)=#2/29/2000#) AND ((Health_History.HeartAttack)=2))
OR (((Health_History.AnginaDOD)=#2/29/2000#) AND ((Health_History.Angina)=2))
OR (((Health_History.IrregularHeartDOD)=#2/29/2000#) AND ((Health_History.IrregularHeart)=2))
OR (((Health_History.OtherHeartDOD)=#2/29/2000#) AND ((Health_History.OtherHeart)=2))
OR (((Health_History.StrokeDOD)=#2/29/2000#) AND ((Health_History.Stroke)=2))
OR (((Health_History.FainitingDOD)=#2/29/2000#) AND ((Health_History.Fainting)=2))
OR (((Health_History.HBPDOD)=#2/29/2000#) AND ((Health_History.HBP)=2))
OR (((Health_History.HighCholDOD)=#2/29/2000#) AND ((Health_History.HighChol)=2))
OR (((Health_History.ThyroidDOD)=#2/29/2000#) AND ((Health_History.Thyroid)=2))
OR (((Health_History.CancerDOD)=#2/29/2000#) AND ((Health_History.Cancer)=2))
OR (((Health_History.KidneyDOD)=#2/29/2000#) AND ((Health_History.Kidney)=2))
OR (((Health_History.LiverDOD)=#2/29/2000#) AND ((Health_History.Liver)=2))
OR (((Health_History.GoutDOD)=#2/29/2000#) AND ((Health_History.Gout)=2))
OR (((Health_History.DiabetesDOD)=#2/29/2000#) AND ((Health_History.Diabetes)=2))
OR (((Health_History.PsychDOD)=#2/29/2000#) AND ((Health_History.Psych)=2))
OR (((Health_History.DrugDOD)=#2/29/2000#) AND ((Health_History.Drug)=2))
OR (((Health_History.EatingDOD)=#2/29/2000#) AND ((Health_History.Eating)=2))
OR (((Health_History.Other1DOD)=#2/29/2000#) AND ((Health_History.Other1)=2))
OR (((Health_History.LastMenstrualPeriod)=#2/29/2000#) AND ((Health_History.Gender)=1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davanderbilt]

That worked, thanks!