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

[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

 

[Joseph Meehan]

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

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

 

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

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.

 

[Jamie Collins]

The 'date' I'm trying to add DOES include a
year. It doesn't include a day! It's MMYY.

Indeed it does include a day element; it also includes hour, minute
and second elements.

A DATETIME value in Access/Jet is an instant in time; the equivalent
data type in the SQL standard is (more appropriately) named
TIMESTAMP.

Is March 2007 an instant in time? No, it's a period. In SQL products
such as Access/Jet that have only one temporal data type, a period
(single atomic fact) is modelled using a start date and end date pair
(same row, two columns). Therefore, the period March 2007 is the
period

[#2007-03-01 00:00:00#, #2007-03-31 23:59:59#]

The square brackets indicates I'm using the closed-closed
representation, where both delimiting DATETIME values are in the
period being modelled. The end date is the last time granule within
the period using the SQL product's smallest supported time granule,
which for Access/Jet is one second. Primarily, I choose closed-closed
because it suits intuitive (human readable) constructs such as

test_date BETWEEN start_date AND end_date

However, it also satisfying 'unequivocal' e.g. if I tell you the
period for submission of entries to a competition is

[#2007-03-22 09:00:00#, #2007-03-22 15:00:00#)

the bracketing this time indicating the representation is closed-open
(note the curved closing bracket), is it clear that an entry submitted
with a timestamp of #2007-03-22 15:00:00# would not be valid? For me
it is not as clear as the equivalent closed-closed representation
i.e.

#2007-03-22 15:00:00# BETWEEN #2007-03-22 09:00:00# AND #2007-03-22
14:59:59#

is obviously false.

Jamie.

--

 

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

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.

 

[Joseph Meehan]

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.

Note: you can enter a month and year and use programming to add the day
(01) and then only show the month and year. However doing this could under
some odd conditions cause some miscalculation. You could set it up to
default to the first of the month and display it that way. Remember there
is a difference in dates in a given month, you should consider possible
future ramifications by not recording the date.

 

[Jamie Collins]

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.

Not so e.g. both #23/03/2007# and #03/23/2007# will be assumed to be
the twenty-third day of March i.e. it assumes the value >12 is the day
and the value <=12 is the month, a good assumption as it happens. If
you want to remove assumption, and I suggest you should, use the ISO
8601 representation (#yyyy-mm-dd hh:nn:ss#) because #2007-23-03
00:00:00# will fail which is a good thing.

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.

Here's another view: it is of no consequence how the values are
stored. Access/Jet SQL is a strongly typed language and DATETIME is a
distinct data type with associated temporal functionality (DATEDIFF,
DATEADD, DATEPART, etc). If you always use date literals with all time
elements showing, and only use temporal functionality with temporal
data then like me you won't have any problems. Sure you can get Jet to
store a double float that is not a DATETIME value in a DATETIME column
but the trick is not to let that happen.

Jamie.

--