update query data type mismatch issue

[Skip Bisconer]

I have two tables TrainingPolicies and TrainingLog. Each policy has an invertal of 1, 3, or 0 indicating years to establish a retrain date in the TrainingLog.

We occasionally have to change the interval and need to update the Trainglog with the interval and change the retrain date to match.

Both tables data type are the same for Interval (Number) and Date (m/d/yyyy) but when I try to run an iff statement I used with a form to calculate the retrain date I get a Data Type mismatch and I am assuming its the date criteria.

If someone can tell from my SQL what my real problem is it would help. Thanks for looking at my request. Any suggestions would be greatly appreciated. Below is my SQL:

UPDATE tblTrainingLog INNER JOIN tblTrainingPolicies ON tblTrainingLog.CWSPolicy = tblTrainingPolicies.CWSPolicy SET tblTrainingLog.Intv = tblTrainingPolicies.Intv, tblTrainingLog.RetrainDate = IIf(Nz("Intv",0)=0,Null,DateAdd('yyyy',"Intv","DateTrained")) the last "DateTrained" doesn't have a space it just seems like in this posting and when I try to change it there isn't a space there.

 

[jal]

What looks suspicious to me is this part:

DateAdd('yyyy',"Intv","DateTrained")

While I agree with enquoting "yyyy", I'm not sure quotes belong around the two column names Intv and DateTrained.

 

[DCrake]

The correct syntax is

IIf(Nz([Intv],0)=0,Null,DateAdd('yyyy',[Intv],[DateTrained]))

 

[Skip Bisconer]

Thanks DCrake,
I copied your nz function in and got a halt indicating [Intv] in the From statement could be ambiguous so I changed t0 the followiing:

IIf(Nz([tblTrainingLog].[Intv],0)=0,Null,DateAdd('yyyy',[tblTrainingLog].[Intv],[DateTrained]))

It didn't give me a halt, however, it didn't change the retrain date either. I ran the update a second time and the retrain date changed. I am going to have to set up two update queries or force a second run on the same query?

 

[Brianwarnock]

Try replacing [tblTrainingLog].[Intv] with [tblTrainingPolicies].[Intv] in the formula , my thinking is that the Intv will not have been changed when you try to do the update.

Brian

 

[Skip Bisconer]

Right on Brian! Good catch...it works perfectly now. Thanks to all of you for your help on this. This is the greatest forum on the net.

 

[Brianwarnock]

I like it when a plan works.

Brian