year-only format

[Guest]

Hi,

if I want only the 4-digit format for a year (e.g., 2006), can I set a
date/time format to something like "yyyy'? I can't get it to work. Or are
my only options to set it to number or text data format? And if I do that,
am I going to run into any problems with a query range?

Thanks!
Lynn

 

[Guest]

If you only want to store a year value use an integer field. Datetime fields
must contain the month and day.

 

[Guest]

Duane,

Thank you for the quick response. How do I limit the field width to 4?

 

[John Vinson]

Hi,

if I want only the 4-digit format for a year (e.g., 2006), can I set a
date/time format to something like "yyyy'? I can't get it to work. Or are
my only options to set it to number or text data format? And if I do that,
am I going to run into any problems with a query range?

What "doesn't work"? "yyyy" is a perfectly good format for a date.

Access will store - and require you to enter - a complete date;
date/time values, regardless of format, are stored as Double Float
numbers, a count of days and fractions of a day since midnight,
December 30, 1899. What will be stored in the table is (say)
39053.9100462963 which is equivalent to 12/2/2006 9:50:28 PM; if you
display that value in a textbox with a format of "yyyy" you'll SEE
just 2006. If you try to edit the textbox though you'll have to type
in a full valid date.

If you only want to STORE a four digit number, and work with it as a
four digit number rather than a precise point in time, then I'd
suggest using an Integer. You can search integerw with a range like

BETWEEN 1994 AND 2001

perfectly well; the only thing that won't work directly is searching
between actual date values. You could, however, use

BETWEEN 1994 AND Year(Date())

to get all values through this year.

John W. Vinson[MVP]

 

[Guest]

Why? You can set a Validation Rule on the text box on your form that would
limit the value to a particular range.

 

[Guest]

Hi Duane,
The code below seems pretty straightforward, but I'm getting a generic
error "The value you entered isn't valid for this field." Do you know what
might be causing this?

If Len(Me.StartYear) <> 4 Then
MsgBox "A 4-digit year is required."
Cancel = True
End If

 

[Guest]

Thanks you so much, Duane. That worked.

I would not use a string function like Len() with a numeric value. You should
use the Validation Rule property of the text box on your form. The Validation
Text property can be used to display a message.

Validation Rule: Between 1000 And 9999
Validation Text: Your Value must be between 1000 and 9999

I would expect if you are capturing a year value you could be a bit more
specific like:

Validation Rule: Between Year(Date())-5 And Year(Date())
Validation Text: The year value must be within the last 5 years

 

[OfficeDev18 via AccessMonster.com]

Hi, guys, it's been a long time since I've been here, but I'd like to try my
hand at this, as I see another angle that hasn't yet been addressed.

Lynn, when you have a line like

TheYear = Format(Date(), "yyyy")

you better make sure that TheYear is a string variable, because the Format()
function returns a string, not a number. If you want a 4-digit numeric year,
you need to tweak your line, like

TheYear = CInt(Format(Date(), "yyyy"))

Otherwise you'll get the error message you got.

Hope this helps,

Sam

Hi Duane,
The code below seems pretty straightforward, but I'm getting a generic
error "The value you entered isn't valid for this field." Do you know what
might be causing this?

If Len(Me.StartYear) <> 4 Then
MsgBox "A 4-digit year is required."
Cancel = True
End If

Why? You can set a Validation Rule on the text box on your form that would
limit the value to a particular range.

[quoted text clipped - 15 lines]