New Old catagory in 2 condition

[naungsai]

Dear Friends

I have a table with the column named, "CODE", "SDATE".
If the query, I will calculate a field named "CATEGORY".
My conditions are
1. If the "SDATE" is before 1/1/2008, I want to put "Old" in the "CATEGORY" column. Otherwise "New".
2. In the "CODE", if the second character of the code is "9" I will also put "Old" in the "CATEGORY" column regardless of "SDATE".

CODE-------SDATE----------CATEGORY
100223-----2/21/2008--------New
100114-----12/10/2007------Old
190012-----2/20/2008-------Old
190005-----11/21/2007------Old
200552-----3/30/2008-------New

Thanks in advance.

 

[Mike375]

I think this gets it:

NewFieldName: IIf(Mid([abc],2,1)=9,"Old",IIf([xyz]<#1/01/2008#,"Old","New"))

Where [abc] has the number and [xyz] has the dates

Not sure how the date thing will be with US date system as compared to Australian but I think it should be OK

 

[Mike375]

PS

Attached DB was used

 

[naungsai]

PS

Attached DB was used

Dear Mike

Thank you. It works.

NS