Format Date

[Guest]

Hi,
I have a field that shows only the month. I want to format it as a date so I
can sort it by month, but the formating options for date all include the day
and the year. Is it possible to make this a date/time field but have it
formatted to only show the month. If not how can I sort the months as a text
field so they go in order (i.e. Jan, Feb, Mar) rather than in alphabetical
order.

thanks,

 

[tina]

first, you have to decide what you're storing - a "real" date, or just a
value. if you are storing real, complete dates, then you can format them
pretty much any way you want. for instance, to "show" only the month, you
can set the Format property of a control in a form or report to

"mmm"

to see Jan, Feb, etc. or to

"mmmm"

to see January, February, etc.

if you decide to only store a value that you can convert to a month, instead
of a real date, then suggest you create a table with two fields, as

tblMonths
MonthNo (primary key)
MonthName

and populate the table, as

MonthNo MonthName
1 January
2 February
3 March
etc...

use the table as the RowSource in a combo box or listbox control in a
*form*, so that the stored value is the number (allowing you to sort
appropriately), but the "droplist" (and displayed) value is the text month
names.

hth

 

[John W. Vinson]

I have a field that shows only the month. I want to format it as a date so I
can sort it by month, but the formating options for date all include the day
and the year. Is it possible to make this a date/time field but have it
formatted to only show the month. If not how can I sort the months as a text
field so they go in order (i.e. Jan, Feb, Mar) rather than in alphabetical
order.

You are mistaken about the date formats. See the entry for custom date formats
in the help - you do NOT need to use only the named format!

I'd use a Format property of

"mmm"

to display just the three letter month abbreviation. If you sort by the date
field itself it will sort chonologically (but of course December 2005 will
sort before January of 2006).

John W. Vinson [MVP]

 

[Guest]

Two ways:

First one is to make a table called tblMonths. Make it looks something like:
Months MonthSort
Jan 1
Feb 2
Mar 3
ect.
When you need to sort by the month, add this table to the query and sort on
MonthSort.

Or make a field in the query something like this and sort on it:
SortMonth: CDate([MonthField] & "-2007")

The above will produce a date datatype with the first day of the month. Note
that if you have a Null value in the MonthField, CDate will evaluate -2007 as
7/2/1894. Therefore you need to have something in each record or trap the
nulls.

Also any mispelling of a month will cause a type mismatch error.

 

[Jeff Boyce]

Karl

Are you actually storing the MonthName? If you have more than one year's
data, how will you tell March, 2003 apart from March, 2004?

Regards

Jeff Boyce
Microsoft Office/Access MVP