Date format!

[Guest]

I am trying to create a way that will display a date in "month date, year"
format (for example "February 21, 2007") but also have the ability to only
enter the "year" or just the "month year" (February 2007). Also, I'm not
sure if this is done as a format or input mask. Any help would be
appreciated!! Thanks

 

[Douglas J. Steele]

Can't be done, at least, not using a Date field.

February 2007 isn't a date. Dates are stored as 8 byte floating point
numbers, 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.

You could, I suppose, store your pseudodates as text, but querying them
would be problematic, to say the least.

 

[fredg]

I am trying to create a way that will display a date in "month date, year"
format (for example "February 21, 2007") but also have the ability to only
enter the "year" or just the "month year" (February 2007). Also, I'm not
sure if this is done as a format or input mask. Any help would be
appreciated!! Thanks

To display a Date as February 21, 2007, simple write:
mmmm dd, yyyy
in the Format property of the Field.

However, a DateTime field must be a valid date it has a month, day,
and year portioin). Entering just a year (i.e. 2007) is NOT a valid
date. Neither is entering a Month and Year... but.... Access will
assume that any entry into a date field, such as 12/2007 will be the
first day of the 12th month, so it will store 12/1/2007. If however,
you enter 12/5 Access will make the assumption that the date is
12/5/current year.
It's not a good idea to make Access assume anything.
Enter a full date. If you don't care about the day of month, enter a 1
as the day anyway.
A mask forces data entry in a particular manner. Often times they
cause more problems than they avoid.

 

[Jamie Collins]

February 2007 isn't a date. Dates are stored as 8 byte floating point
numbers, where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and thedecimalportion represents the time as a
fraction of a day.

You could, I suppose, store your pseudodates as text, but querying them
would be problematic, to say the least.

Access/Jet's DATETIME data type (broadly equivalent to standard SQL's
TIMESTAMP) is used to model an instant in time and is accurate in to
the nearest second. With due respect, who cares how they are stored:
fixed decimal, scaled integer, ticks, text, etc? Not I. With DATETIME
data, if you have effective database constraints and use only the
built-in temporal functions (DATEDIFF, DATEADD, DATEPART, etc) then
you won't go wrong.

February 2007 is a period. Periods are modelled in SQL using a start-
and end date pair of instants in the same row. You get to choose the
representation; I prefer closed-closed representation where the start
date and end date values are the first and last time granules
respectively in the period e.g. February 2007 would be

[#2007-02-01 00:00:00#, #2007-02-28 23:59:59#]

The main advantage of the closed-closed representation is that BETWEEN
constructs can be used, making SQL code 'human friendly' which aids
maintenance e.g.

WHERE test_date BETWEEN start_date AND end_date

Jamie.

--