Control Source Equation (1 Viewer)

[ernielippert]

My form contains a series of check box controls RecdCard2000, RecdCard2001, RecdCard2002… RecdCard2012, RecdCard2013 … . There is another check box, Last Year, which currently has for its Control Source: RecdCard2011. Every year I change this manually. I want to change the Control Source to: ="RecdCard" & Right(Str(Year([Date])-1),4) so that it automatically updates each year. However, this expression does not work. The Last Year check box on the form is greyed out. Why?
Thanks,
Ernie

 

[apr pillai]

Remove the square brackets [] appearing around the Date() function. The Str() function is not necessary. The Right() function automatically converts the Year into a string before it extracts the rightmost 4 digits.

In fact you can use the following expression for the same result:

 ="RecdCard" & Year(Date())-1

When numeric value is concatenated with a string literal the number is automatically converted into a string.

 

[ernielippert]

Thanks Apr Pillal,
I entered ="RecdCard" & Year(Date())-1 as you suggested as the Control Source but the check box on the form is still greyed out. I entered the expression in the Immediate Window and it works fine as shown:

a="RecdCard" & Year(Date())-1
?a
RecdCard2011

Thnks for your help.
Ernie

 

[ernielippert]

The Control Source equation should be =Form("RecdCard" & Year(Date())-1).
regards,
Ernie