Update query stopped working

[Ted Allen]

Hi Shanin,

Is [DateTaken] null in any of the records in the table?
If so, I believe this would cause a problem. Post back
if that wasn't it.

-Ted Allen

-----Original Message-----
Any ideas on this. I had an update query that would compare the expiration
date on a training compared to the current date. If the current date was
greater than the expiration date, it would mark the column "late" with "yes".
This worked when I first made it and I have been working with other items,
now I get a data type mismatch in criteria expression. The expiration date
is calculated by an expression since in my form I have the control set to my
Where statement in my SQL. Here is my SQL

UPDATE tblTraining SET tblTraining.Late = Yes
WHERE (((IIf([Training]="1st
aid",DateSerial(Year([DateTaken])+3,Month ([DateTaken]),Day([DateTaken])),IIf([Training]="cpr"

Or
[Training]="tb",DateSerial(Year([DateTaken])+1,Month

([DateTaken]),Day([DateTaken])),IIf([Training]
="pdt",DateSerial(Year([DateTaken])+1,Month
([DateTaken]),Day([DateTaken])),IIf([Training]
="med",DateSerial(Year([DateTaken])+2,Month
([DateTaken]),Day([DateTaken])),IIf([Training]="pbs"

Or
[Training]="abuse",DateSerial(Year([DateTaken])+2,Month

([DateTaken]),Day([DateTaken])),IIf([Training]="fire"
Or [Training]="driving" Or [Training]="lifting" Or
[Training]="hippa",DateSerial(Year([DateTaken])+1,Month ([DateTaken]),Day([DateTaken])))))))))<Now()));



.

 

[Ted Allen]

My pleasure, glad it helped.

-Ted Allen

-----Original Message-----
That was it, there were two missing a DateTaken. Thanks so much. I"m going
to change that to required.

Hi Shanin,

Is [DateTaken] null in any of the records in the table?
If so, I believe this would cause a problem. Post back
if that wasn't it.

-Ted Allen

-----Original Message-----
Any ideas on this. I had an update query that would compare the expiration
date on a training compared to the current date. If

the
current date was

greater than the expiration date, it would mark the column "late" with "yes".
This worked when I first made it and I have been working with other items,
now I get a data type mismatch in criteria

expression.
The expiration date

is calculated by an expression since in my form I

have
the control set to my

Where statement in my SQL. Here is my SQL

UPDATE tblTraining SET tblTraining.Late = Yes
WHERE (((IIf([Training]="1st
aid",DateSerial(Year([DateTaken])+3,Month ([DateTaken]),Day([DateTaken])),IIf([Training]="cpr"

Or
[Training]="tb",DateSerial(Year([DateTaken])+1,Month

([DateTaken]),Day([DateTaken])),IIf([Training]
="pdt",DateSerial(Year([DateTaken])+1,Month
([DateTaken]),Day([DateTaken])),IIf([Training]
="med",DateSerial(Year([DateTaken])+2,Month
([DateTaken]),Day([DateTaken])),IIf([Training]="pbs"
Or
[Training]="abuse",DateSerial(Year([DateTaken])

+2,Month
([DateTaken]),Day([DateTaken])),IIf([Training]="fire"

Or [Training]="driving" Or [Training]="lifting" Or
[Training]="hippa",DateSerial(Year([DateTaken])

+1,Month
([DateTaken]),Day([DateTaken])))))))))<Now()));

.

.