Date format | Month/Year only changes to Day/Month/CurrentYear

P

Paul

Hi Y'all,

I have a table field which I've formatted to MM/YY. On a form the
input box for that field is formatted the same. However if I enter a
date such as 03/08 (March 2008) it becomes 08/07. Access sees 03/08 as
3rd August and adds the current year. The data stored is 03/08/07.

How can I stop this happening? I assume I can just use a formatted
number field but surely there's a way to keep the date format?

Thanks in advance!

Paul
 
P

Paul

"03/08" is not a date. A date includes a year. If you are not going to
do any calculations, you may want to use text rather than date. BTW Access
does not save dates as "dates" but as a number with the decimal part being
the time.

You can display a date in the Month Day format if you like but Access
always stores it as a number. At the moment I can't remember what date is 0
but for dates before the numbers are negative and after are positive. A year
after that zero date would be 365 or 366.
Click to expand...

Hi,

Thanks for your reply. The 'date' I'm trying to add DOES include a
year. It doesn't include a day! It's MMYY. Ideally I would like to be
able to keep it as a date as calculations may be necessary in the
future. But if not, it's not the end of the world.
 
F

fredg

Hi,

Thanks for your reply. The 'date' I'm trying to add DOES include a
year. It doesn't include a day! It's MMYY. Ideally I would like to be
able to keep it as a date as calculations may be necessary in the
future. But if not, it's not the end of the world.
Click to expand...

You seem to be confusing how a date is entered with how it is stored
and how it is displayed.
An entered date must have a valid month, day, and year component, as
well as a Time component. The time part may be omitted, in which case
Access assumes midnight. If you enter 03/08, Access will assume you
mean 03/08/Current Year. If you enter 03/07 Access will assume the 07
is the current year and assume the 1st day of the month, i.e.
03/01/07. To further confuse the situation, if you enter 03/08/98,
Access will assume you mean 03/08/1998.
Of course if you enter a month, day and 4 digit year Access doesn't
need to make any assumptions at all, which is always best.

In any event, the value will be stored as a double number, counting
the number of days from a beginning date of 12/30/1899. So 03/08/07
will be stored as 39149.0, the .0 part being midnight.

The display is a different matter. You can display a valid date in
many different fashions, some being Month/day; Month/Year; Year;
Month; Day; Quarter, etc.

The best way to solve your entry dilemma is to enter a complete date
value, month/day/year.
Then display just the month and day.
 
Top