Calculated control

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a form with a control that is calculated using this
formula
="FLA" & "." & [DocIndex] & "." & Year([DateCreatedtxt])
The format of DateCreatedtxt is Short date but returns a
value for year as 2003
2 questions
How can I ensure that the year is returned as 03 instead
of 2003? and
How can I get the value of this calculated control which
is called say Docnbr into a field called DocNbrtxt in my
table tblDocuments?
TIA
Tony
 
Tony,

1) The Year() function returns a Variant (Integer) value, not a valid date,
so formatting the result as a date won't work. You could have used
Right(Year([DateCreatedtxt]),2)
but there is no need to do so.

Try it this way
="FLA" & "." & [DocIndex] & "." & Format([DateCreatedtxt],"yy")

As an aside, I would strongly suggest you start using 4 digit years.
Access will assume the current century of 2 digit dates up until 1/1/2030,
at which time an entry of 1/1/30 will be assumed as 1/1/1930.
That time will be here before you know it.
Don't put yourself in the position of having to make your own Year 2030 Date
fix.

2) Regarding the saving of the expression data to a table.
Unless there is a compelling reason, Don't!
Whenever you need the combined data in a form, report, or query, just
use the expression again.
 
Thanks Fred I will heed your advice!!
-----Original Message-----
Tony,

1) The Year() function returns a Variant (Integer) value, not a valid date,
so formatting the result as a date won't work. You could have used
Right(Year([DateCreatedtxt]),2)
but there is no need to do so.

Try it this way
="FLA" & "." & [DocIndex] & "." & Format ([DateCreatedtxt],"yy")

As an aside, I would strongly suggest you start using 4 digit years.
Access will assume the current century of 2 digit dates up until 1/1/2030,
at which time an entry of 1/1/30 will be assumed as 1/1/1930.
That time will be here before you know it.
Don't put yourself in the position of having to make your own Year 2030 Date
fix.

2) Regarding the saving of the expression data to a table.
Unless there is a compelling reason, Don't!
Whenever you need the combined data in a form, report, or query, just
use the expression again.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Tony Williams said:
I have a form with a control that is calculated using this
formula
="FLA" & "." & [DocIndex] & "." & Year([DateCreatedtxt])
The format of DateCreatedtxt is Short date but returns a
value for year as 2003
2 questions
How can I ensure that the year is returned as 03 instead
of 2003? and
How can I get the value of this calculated control which
is called say Docnbr into a field called DocNbrtxt in my
table tblDocuments?
TIA
Tony
Click to expand...


.
Click to expand...
 
Back
Top