Criteria

[Tronsliver]

I have a table called SIDPERS with two fields. The first field is named sidstrPAY_GR & the other is siddatDOR_RES.

sidstrPAY_GR is a text field and has either a "O5" or "O4" entry. siddatDOR_RES holds dates.

What I would like to do is build a claculated field that adds 4 years to the date in siddatDOR_RES if sidstrPAY_GR = "O5" and two years if sidstrPAY_GR = "O4".

I'm a newbie so any help would be appreciated.

 

[tlbroadbent]

UPDATE SIDPERS SET SIDPERS.siddatDOR_RES = IIf([sidstrPAY_GR]="05",DateAdd("yyyy",4,[siddatDOR_RES]),IIf([sidstrPAY_GR]="04",DateAdd("yyyy",2,[siddatDOR_RES]),DateAdd("yyyy",0,[siddatDOR_RES])));


Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Tronsliver]

Terry,

Thank you I'm on the right track now. However, I worded my original question incorrectly. I apoligise. It should be:

I have a table called SIDPERS with two fields. The first field is named sidstrPAY_GR & the other is siddatDOR_RES.

sidstrPAY_GR is a text field and has either a "O5" or "O4" entry. siddatDOR_RES holds dates.

What I would like to do is build a claculated field that adds 4 years to the date in siddatDOR_RES if sidstrPAY_GR = "O5" and two years if sidstrPAY_GR = "O4".

The calculated field is on a report so the 4 years that are added to siddatDOR_RES would need to go into the field on the report instead of updating the table. Can you still help?

Thanks and sorry for the misunderstanding.

I'm a newbie so any help would be appreciated.

 

[tlbroadbent]

No problem!

Select sidstrPAY_GR, IIf([sidstrPAY_GR] = "05", DateAdd("yyyy",4,[siddatDOR_RES]), DateAdd("yyyy",2,[siddatDOR_RES])) As siddatCalculated;

Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Tronsliver]

Terry,

Do I put this equation inside the calculated field and then name the field siddatCalculated?

Also, should there be an "=" sign after Select sidstrPAY_GR in the formula or should it stay a comma?

Roger

 

[tlbroadbent]

I'll break the query down to component parts.

Select sidstrPAY_GR,
' This selects sidstrPAY_GR for display in the result. The comma separates this item from the next in the select list.

IIf([sidstrPAY_GR] = "05",
' Test if sidstrPAY_GR = "05"

DateAdd("yyyy",4,[siddatDOR_RES]),
' If the test evaluates true then use this calculation.

DateAdd("yyyy",2,[siddatDOR_RES]))
' Else use this calculation

As siddatCalculated;
' Give the calculated field a name

In the query designer grid you would select sidstrPAY_GR in teh first column and insert the entire IIF statement in teh 2nd column like this...

siddatCalculated: IIf([sidstrPAY_GR] = "05", DateAdd("yyyy",4,[siddatDOR_RES]), DateAdd("yyyy",2,[siddatDOR_RES]))

Hope this helps. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm