date default problem

[Guest]

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?

 

[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.

 

[Guest]

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?

 

[Peter R. Fletcher]

What you can do (for a text field) is allow the user to input the data
in whatever format you think is reasonable and recognisable, using
code in the Control's BeforeUpdate Event to validate this, and then
use code in the Control's AfterUpdate Event to convert it into the
format you want to use for storage and display.

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?

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Guest]

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

 

[Peter R. Fletcher]

What you are trying to do (like most non-trivial uses of Access)
requires a reasonably facility and comfort with coding. Trying to
guide you through the specific issue, if you don't possess that, is
not likely to be a very effective use of time (particularly mine!). I
suggest that you get a good Access book (e.g. Litwin et al.'s Access
200n Desktop Developer's handbook) and work through it. That will give
you a good grounding in what you need to know to use Access
effectively. I and other people in this NG will be happy to help with
specific problems, but we can't learn Access or Access coding for
you.

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

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher