Having a date column with just month and year

[suroora]

I have a database of publications in Access. I want to change the date
published column to just month and year instead of the way it is now--date,
month and year. We don't have exact dates books were published, so the date
part doesn't really serve a purpose. But I can't find a way of doing this.

I'm using Access 2003.

Thanks,
Suroor

 

[ghetto_banjo]

keep the data type as Date/Time, but then change the Format to:

mm-yyyy


that would store 01-2010, 02-2010, etc


you can setup the format to whatever makes sense to you. yyyymm, yyyy-
mm, mm/yyyy, mm-yy, etc.

 

[suroora]

Thanks!

 

[Sylvain Lafontaine]

Beside changing the format as explained in the other answer, you could also
use a character field instead of a DateTime field. The DateTime fields are
convenient when you want to calculate with them; for example adding or
subtracting a certain number of days but in the case of the publishing date
of a book, it's more like a telephone number: you don't really want to use
that in an expression.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[suroora]

Can I get it to spell out the month, eg, June 2001?

 

[John W. Vinson]

I have a database of publications in Access. I want to change the date
published column to just month and year instead of the way it is now--date,
month and year. We don't have exact dates books were published, so the date
part doesn't really serve a purpose. But I can't find a way of doing this.

I'm using Access 2003.

Thanks,
Suroor

A Date/Time field is actually stored as a Double number, a count of days and
fractions of a day since midnight, December 30, 1899. As such it's a precise
point in time, accurate to a few microseconds (though it can only be displayed
to the second). "October 1982" isn't a precise point in time!

I'd suggest storing a date/time field, just using the first of the month as
the date. As Ghetto-banjo suggests, you can set the Format of the control in
which you display the date to show only those parts of the date of interest,
e.g. a format of "mm/yyyy" will show "10/1980".

 

[Petr Danes]

Can I get it to spell out the month, eg, June 2001?

Sure. The string 'm' for month can be used in four different forms:

m - month as one or two digit number, as appropriate
mm - month as two digit number, with leading zero if needed
mmm - month in Roman numerals
mmmm - month with full name of month spelled out

Pete

 

[Maarkr]

mmmm yyyy

Can I get it to spell out the month, eg, June 2001?

 

[Jeff Boyce]

A niggling little detail ... using Format would change the display of what
is stored, but wouldn't change the value that is stored.

Regards

Jeff Boyce
Microsoft Access MPV

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[suroora]

Thanks. It worked!

mmmm yyyy

 

[suroora]

Thank you, everyone! That was very helpful.

A Date/Time field is actually stored as a Double number, a count of days and
fractions of a day since midnight, December 30, 1899. As such it's a precise
point in time, accurate to a few microseconds (though it can only be displayed
to the second). "October 1982" isn't a precise point in time!

I'd suggest storing a date/time field, just using the first of the month as
the date. As Ghetto-banjo suggests, you can set the Format of the control in
which you display the date to show only those parts of the date of interest,
e.g. a format of "mm/yyyy" will show "10/1980".

 

[JeanPaulo]

Thank you, everyone! That was very helpful.

I have been doing it for years for artist's birthday. We may know
only year, month or all.

I just set 3 different integer fields. It is easy to recombine them
if needed, or to valid them. And you let unused to blanks.