Solved: Month/Year date format

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Encountered a small but irritating problem between Access 2000 and Access 2003. Designed a table and input form in Access 2000 that contain a date field to capture credit card expiration dates, which are always only month and year. Made the format "m/yy" with an input mask of #0/00;0;_. Works perfectly in my Access 2000, but when I installed the application on a client's (non-profit org.) computers, which have Access 2003, every entry of a year other than this year (06) gets changed to 06. E.g., if the user enters 0508 and tabs to the next field, it displays "5/06". It behaves as though it thinks you entered only the month and DAY, but since the format has to be m/yy, it displays the 5 and tacks on the current year.

Access (at least Access 2000) does not offer a built-in month/year format. So how can I make the field understand that what goes in there is supposed to be only month and year? Is it possible to do with a Date/Time field type, or do I need to make it Text, or what?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,435
i'm a surprised on the M/YY format - how doesOct Nov Dec work 10/11/12
should the format be MM/YY
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
You can make the format anything you want. It controls only what's displayed, not what the system stores. If I make the format "mm/yy", for single-digit months it will display "05/06". With the "m/yy", it displays "5/06" and for double digit months, 12/06. However, what the system stores is the Julian date (today, for example, is 38801). That may be the problem because if you establish a field with a data type of "Date/Time", Access 2003 might require the full date in order to store the Julian number. But why doesn't Access 2000?
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Upon posting this message, I'll mark it as solved. Reason: The problem has nothing to do with differences between Access 2000 and -2003. I just retried entering a month/year other than this year, and my Access 2000 does the same thing -- changes the year to this year regardless of what you enter. I have concluded that a month/year field CANNOT be entered in the table definition as "Date/Time"; it MUST be "Text". If one thinks about it, a "month/year" entry IS NOT A DATE! Access stores all dates in Julian format and cannot store a month/year entry in that format because there is no DAY.

I apologize for cluttering the board. Next time, I will make sure brain is in gear before engaging mouth!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top