Converting Date text to Value

[bearcub]

I have a table which is supposed to have only a Year ended but someone added something that looks like a date.

The field should show 2016 as opposed to 6/25/16. We are using this data to generate directories for our members.

The field was initial set up as a text field.

The formula I was tried initialy in the query was:

=Year(VAL([Term_Year]))

Instead of getting 2017 and 2018 per the field entries each cell had 1905. I did use the Val function and got the correct year but one field was empty so I got a "#Error" message.

How do I fix these so I don't have to go back and manually correct the data?

Thank you for your help in advance,

Michael

 

[Joe4]

You need to convert it to a Date, not a Value, i.e.

=Year(DateValue([Term_Year]))

 

[bearcub]

ah, thank you , that was the problem.

I did try to get rid of that null value and fix the field that had "6/25/2017" with the following formula:

IIf(IsNull([State Council Term End])," ",
IIf([State Council Term End]="##/##/##",
Year(DateValue([State Council Term End])),
Val([State Council Term End])))

Everything works fine except that I get "6" in the 6/25/2017 field. How do I get around this? It looks lik 6/25/2017 is being replaced by 6. I even tried "20"&Right([State Council Term End],2) but got the same result. I think problem is that I'm not providing the formula with the correct value for that "6/25/2017" field. Who knows, maybe in the future I might have something like "12/01/2017" so I need something to fix potential issues.

Any suggestions?

Thank you, as always for your help

Michael

 

[Joe4]

Can you provide us with samples of all the different values/formats you may get in this field?

 

[bearcub]

I'm getting three different value type. The field is supposed to be a Year field (but the data type is set up as text). Could I convert the data type to year only or would this create havoc with the outlier?

2016
2017
2018
2019
(a couple of fields with an empty field)
6/25/2017

I have 255 records and these are the distributed values.

I have 2 or 3 blank values (which is why I had to include the first Iif statement), and then I 1 "6/25/2017", and the remainder are 2016-2019 values,

Does this help

Like I mentioned, I presently have accounted for the Null value but I still have the problem field returnin the 6 in the "6/25/2017" field.

Thank you for your help,

Michael

 

[Joe4]

This will return the results as a Text value, but should handle all the different scenarios you presented.

IIf(Nz([Term_Year],"X")="X","",IIf(InStrRev([Term_Year],"/")>0,Mid([Term_Year],InStrRev([Term_Year],"/")+1),[Term_Year]))

If you need it to actually be a number, try this variation:

IIf(Nz([Term_Year],"X")="X",0,IIf(InStrRev([Term_Year],"/")>0,Mid([Term_Year],InStrRev([Term_Year],"/")+1),[Term_Year]))+0