if statement in UPDATE

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have a table of dates. The year, month and day are three separate fields.
When I make my table, I'm including three extra fields, one for each, empty
text.

Next I want to make a pass to fix the years. I'm trying to use:

UPDATE
tblWtrMeterCount_UT220AP_NonActive

SET
tblWtrMeterCount_UT220AP_NonActive.[Init Date YY] = if([Init YY]>79,"19" &
text([Init YY]),if([Init YY])<10,"200" & text([Init YY]),"20" & text([Init
YY]));

But I'm getting an error:

"Undefined function 'if' in expression."

Can I not do what I want to do? If not, then how?

Thanks in advance,

Tom
 
You should use iif instead of if
UPDATE
tblWtrMeterCount_UT220AP_NonActive

SET
tblWtrMeterCount_UT220AP_NonActive.[Init Date YY] = iif([Init YY]>79,"19" &
Cstr([Init YY]),iif([Init YY]<10,"200" & Cstr([Init YY]),"20" & Cstr([Init
YY])))
 
Back
Top