How to input only years in Date/Time format

M

May

How can i format a text box where the user only need to key in the year
without keying in the month and date. I tried to put down "yyyy' under
Format. However, when i key in 2001 only, i get an error message.

Pls advice.

Thanks.
 
J

Jeanette Cunningham

May,
the easiest way for your users would be to have a drop down list and select
the year.

Jeanette Cunningham
 
M

May

You mean i have to key in the possible number of years for the drop down list?

Thanks.
 
S

Steve Schapel

May,

To me it probably makes most sense for this to *not* be a Date/Time data
type field. I would use a Number (Integer) data type.

A Date/Time data type field *must* contain a day and month component as
well as the year. It is not possible for this to be otherwise. So in
the case of just entering the Year part, what would you otherwise want
to be recorded for the day and month?... 1st January? There are ways
that this type of thing could be made to happen, i.e. an unused
predetermined day and month value to be quietly entered behind the
scenes - if there was a good reason to, which is probably not the case here.
 
K

Ken Sheridan

Not necessarily. You could create a table Years with a single column
YearNumber of integer number data type. Make this column the primary key.
Then create the following function and call it to fill the table, entering
the years you want the list to start and end at when prompted:

Public Sub FillYears()

Dim dbs As DAO.Database
Dim strSQL As String
Dim intStart As Integer, IntEnd As Integer
Dim n As Integer

Set dbs = CurrentDb

intStart = InputBox("Enter start year")
IntEnd = InputBox("Enter end year")

For n = intStart To IntEnd
strSQL = "INSERT INTO Years " & _
"VALUES(" & n & ")"
dbs.Execute strSQL
Next n

End Sub

For the RowSource property of the combo box use:

SELECT YearNumber FROM Years ORDER BY YearNumber;

If you wish you could set the DefaultValue property of the combo box to
Year(Date()) so it will show the current year by default.

Ken Sheridan
Stafford, England
 
B

BruceM

What do you mean by "number of years"? I thought you were wondering about
years such as 2007 and 2008. If you need a list of years and you don't want
to add to the list each year you could make a table containing a listing of
years (either as a number or text field) extending to whatever future year
you choose, then use a query to limit the top value to the current year by
using a criteria expression such as:
<=Year(Date())
If there are to be so many years that you don't want to key them in one at a
time there are ways to automate adding records.
If you want to store an actual date/time value you could do that, but it is
a bit more complex. You would need to decide what date to use (Jan. 1? Dec.
31?), as has been mentioned.
If the record already has a date field containing the current year you don't
need to (an shouldn't) add a separate field for the year. Instead, use
something like this in the control source of an unbound text box:
=Format([DateField],"yyyy")
 
D

Douglas J. Steele

An even easier solution is to create a custom function that provides the
data for the combo box. See, for example,
http://msdn2.microsoft.com/en-us/library/aa196557(office.11).aspx

If you add the following code to your form's module, and set the
RowSourceType property of the combo box to ListYears, the combo box will
contain 9 years in it, ranging from 4 years prior to this year to 4 years
after this year.

Function ListYears( _
fld As Control, _
id As Variant, _
row As Variant, _
col As Variant, _
code As Variant _
) As Variant

Select Case code
Case acLBInitialize
ListYears = True
Case acLBOpen
ListYears = Timer
Case acLBGetRowCount
ListYears = 9
Case acLBGetColumnCount
ListYears = 1
Case acLBGetColumnWidth
ListYears = -1
Case acLBGetValue
ListYears = Year(Date) + (row - 4)
End Select

End Function
 
K

Ken Sheridan

Using a call-back function had occurred to me. Even easier nowadays would be:

Private Function ListYears()

Dim intYear As Integer

Me.cboYears.RowSourceType = "Value List"

For intYear = Year(Date) - 4 To Year(Date) + 4
Me.cboYears.AddItem intYear
Next intYear

End Function

called as the form's On Load event property.

Ken Sheridan
Stafford, England
 
L

Larry Linson

May said:
Just wondering whether its possible.
But thank you for your suggestion.
Click to expand...

A Date/Time Field or Variable represents a point in time... that is a date
and time. You can use parts of it, but it's not particularly easy nor
desirable to use it to represent "just a year", or "just a month", or "just
the time of day" and it's just not suitable for many purposes when you do.

For example, if you only enter time, it will actually be stored (in
Date/Time's own unique format*) as the time you entered, with a "zero" date
which means December 30, 1899. Results from calculations can, thus,
sometimes be "an adventure."

* stored in a Variant, in Double-Precision Format, where the
whole-number part represents days since (or before, if negative)
12/30/1899, and the fractional part represents time since
midnight.

Larry linson
Microsoft Office Access MVP
 
Top