Extract Year from text field

[dj_mix]

I have the following data in the field MA-84

I created a query and added the following

Year: "20" & Right([TYPEFY],2)

when I run the query I get a new field named Year and the format looks like

2084

Now I just need a IIF statement that looks at 2084 and compares to to current year is the current year is less than 2084 leave alone else make it 1984

One last question if the Field TYPEFY IS BLANK how do I make the query do nothing to Newly created query field Year?

 

[Dreamweaver]

Whats The data in [TYPEFY] IE is it a date if so you could use Year([TYPEFY]) in the query.

mick

 

[dj_mix]

Whats The data in [TYPEFY] IE is it a date if so you could use Year([TYPEFY]) in the query.

mick

The field is a text field which usually looks like these:

MA-04
CA-02
BW-99
MA-MAD-02

 

[Road_Warrior]

Here ya go

Year: IIf("20" & CInt(Right([TYPEFY],2))<2084,"20" & Right([TYPEFY],2),"1984")

 

[dj_mix]

Year: IIf("20" & CInt(Right([TYPEFY],2))<2084,"20" & Right([TYPEFY],2),"1984")

I guess I didn't explain it well, but I was able to modify your code but need one bit of help

Year: IIf(CInt(Right([TYPEFY],2))<=DatePart("yyyy",Now()),"20" & Right([TYPEFY],2),"19" & Right([TYPEFY],2))


how do I modify this DatePart("yyyy",Now()) to current year format "yy"


also how do take care of blank TYPEFY fields? right now it says #error in query field ?

 

[dj_mix]

More of a update got the blank taken care of

Year: IIf([TYPEFY] Is Null,"",IIf(CInt(Right([TYPEFY],2))<="06","20" & Right([TYPEFY],2),"19" & Right([TYPEFY],2)))

instead of using )) "06" how do I convert it to current year format "yy"

 

[dj_mix]

Year: IIf([TYPEFY] Is Null,"",IIf(CInt(Right([TYPEFY],2))<="06","20" & Right([TYPEFY],2),"19" & Right([TYPEFY],2)))

This code won't work if I have 2001-2005 it will give me years 1901-1905

anyone every run into this problem ? I would appreciate any help.

 

[Road_Warrior]

Try This

Year: IIf(IsNull([TYPEFY]),"",IIf(Right([TYPEFY],2)<=Right(Year(Now()),2),"20" & Right([TYPEFY],2),"19" & Right([TYPEFY],2)))

 

[Road_Warrior]

Here are the results

TYPEFY Year
MA-84 1984
ma-05 2005
ma-06 2006
ma-07 1907
ma-21 1921

MA-01 2001

 

[dj_mix]

cool it looks like its works, thanks

 

[Road_Warrior]

you're welcome

 

[dj_mix]

hmm , this might be a problem
ma-07 1907

since we add new projects for 07 now..

I may need to add a buffer to the "current year + 1"

This is the code I ended up using and works great "current + 1 year"

Year: IIf(IsNull([TYPEFY]),"",IIf(Right([TYPEFY],2)<=Right(Year(Now())+1,2),"20" & Right([TYPEFY],2),"19" & Right([TYPEFY],2)))