Parenthesis trouble

[Robert Nelson]

I am trying to write an expression to check the year in a date field and if it is equal to a certain value, I want to add a number of years to the year part of the date field.

BUT, the parens are killing me!!!
PLEASE help me with correct syntax and THANK YOU for it!!!
I have tried the following and variations of it, changing number of right & left parens to no avail:

IF(((YEAR(svc_date) = 0101)),ADDYEARS(svc_date,1899))

Thank you again foe your help!!!

Sincerely, RLN

[crimmelcp]

RE: Parenthesis trouble

Robert:
Try
I have tried the following and variations of it, changing number of right & left parens to no avail:

IF(YEAR(svc_date) = 0101,ADDYEARS(svc_date,1899),year)
Charlie Crimmel

[Allen Palmer]

RE: Parenthesis trouble

Robert:

As I read your note, if you have a bad year portion of the date, i.e. year=0101, you want to correct to a real year.

If I've understood your question, a simple global update should work quite easily.

svc_date=if(YEAR(svc_date)=101,ADDYEARS(svc_date,1899),svc_date)

This expression will add 1899 years to every svc_date year that equals 0101. It won't change any other dates. You don't need the leading zero because the year function produces an ordinary number, not a character field. The only potential problem you might have is if a svc_date field is blank. This will produce an error but you can restrict the update calculation to non blank fields.

Hope this approach helps.

Al Palmer

[Robert Picard]

RE: Parenthesis trouble

Hi Robert

I do not know why Charlies exression doesn't work because it looks okay.

My expression works okay for me;

if(year(CNTCT_DATE)=0101,ADDYEARS(CNTCT_DATE,1899),CNTCT_DATE)

so yours should work like this;

if(year(svc_DATE)=0101,ADDYEARS(svc_DATE,1899),svc_DATE)

Good Luck

Robert

[Robert Nelson]

RE: Parenthesis trouble

Al & Robert & Charlie,

Thank you for your response to my problem.
Al - your syntax worked for me, (thank you, thank you),and Roberts is the same as yours, but Charlie has the wrong last parameter name in expression.
But I am a little confused as to why the expression needs that last svc_date
parameter. Is it needed by the IF stmt?
Could one of you please explain it to me?

Anyway - thank you again for the solution.

Sincerely, RLN

[crimmelcp]

RE: Parenthesis trouble

Robert:
You are correct. I typed the wrong value in the last part of the formula.
The if statement should read like this.
IF(YEAR(svc_date) = 0101,ADDYEARS(svc_date,1899),svc_date)

When you think about IF statements, think of the commas as the words THEN and ELSE.

IF(YEAR(svc_date) = 0101 THEN (first comma)
ADDYEARS(svc_date,1899) 'add years to date
ELSE (second comma)
svc_date 'leave svc_date as it is
END IF

If year = 0101 then do first operation else do the second operation which is actually leave svc_date as it is.
If you were doing the operation on a character field such as firstname and had the expression
IF(lastname="SMITH","JOHN","")
it would actually blank out all the first names where the last name did not equal "SMITH"

IF(lastname="SMITH","JOHN",firstame)
This would keep the firstname as it was entered and only change the first name of those people with the last name of "SMITH"

Charlie Crimmel