date default problem

C

caleb

This is an Access 2003 Issue:

One of the fields in my table is a date type field. The user will input the
Month and Year only (no day in other words).

So the format is currently set to: mmm yy

The problem is when trying to input dates from 2004 the field is defaulting
to 2005. Example: if the user enters Dec 04 into the field, Dec 05 is what
appears; or if you enter Nov 04, you get Nov 05.

I have checked the format for the table as well as the format for the Form
(where the actual input is occurring) and I've got the format set to: mmm yy,
for both.

Does anyone know how to correct this issue?
 
D

Douglas J. Steele

The problem is that you're misusing the data type. The Date data type is
intended to store a date. This is because under the covers, it's actually
being stored as an 8 byte floating point number, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899 (and the
decimal portion represents the time as a fraction of a day). Since you're
not giving Access enough information, it's making the best guess it can,
which is to accept the input as a month and day, and appending the current
year.

You may have to change the data type to text.
 
C

caleb

Thanks Douglas. That makes sense.

My only concern with changing it to a text field is not getting data
consistently in the format I need. Is there a way to format the txt field so
that it will do that?

Example: If I always want to see Jan 05, or Dec 04, or Nov 04, etc, vs
seeing JAN05, or JAN 05, or jan 05, or jan05, etc how can I insure the user
always inputs it in the proper format or gets an error msg? I tried to play
around with the validation rule and input masks in the table but can't get
anything to work. I don't know enough about code to make it happen like it
could. Maybe there isn't even a way to do so...but if there is, do you know?
 
C

caleb

I don't know enough about code to write that. Can you give me like just a
small example of how to proceed?
 
Top