date formatting troubles

[newber]

I'm running into some issues with access in formatting dates correctly. I'm running a query that sums values based on months, so I have a column like the following

MONTH: DatePart("m",[SOME_DATE_COL]

So, I'm calculating values on a monthly basis. This works all fine and dandy... It gives me all the results on a monthly basis, regardless of the day... but the months are given in numerical format. Needless to say, not the finest in usability. So I wanted to try something like the following

MONTH: Format((DatePart("m",[DOCUMENT_DATE])),"mmm"

So rather than getting 1, 2, 3... I would get JAN, FEB, MAR... But for some reason it returns JAN regardless of what the number is. If I run the query without the formatting I get an array of numbers correctly representing the months: 1, 2, 3,...,11,12 The moment I add the formatting code all I get is JAN down the column

Any ideas on what might be causing this? It appears that access is only picking up the 1 and ignoring anything else? I've run out of ideas on how to get this to work. Any suggestions or workarounds would be highly appreciated

Thanks mucho

 

[fredg]

See my comments interspeced below.

I'm running into some issues with access in formatting dates
correctly. I'm running a query that sums values based on months,
so I have a column like the following:

MONTH: DatePart("m",[SOME_DATE_COL])

Month is a reserved word in Access/VBA and should not be used as a
field or column name. See Microsoft KnowledgeBase article:

109312 'Reserved Words in Microsoft Access' 209187 'Acc2000:
'Reserved Words in Microsoft Access' 286335 'ACC2002: Reserved Words
in Microsoft Access'
Change the column name to something like MonthOfService, or MonthName,
etc.

So, I'm calculating values on a monthly basis. This works all fine
and dandy... It gives me all the results on a monthly basis,
regardless of the day... but the months are given in numerical
format. Needless to say, not the finest in usability. So I wanted
to try something like the following:

MONTH: Format((DatePart("m",[DOCUMENT_DATE])),"mmm")

The above DatePart("m") value will always be a number from 1 thru 12.

If you then try to format 1 thru 12 as a Month Name, it will always be
January.
To explain this, let's assume the month number returned by the
DatePart function is 12 (December).
You then get
=Format(12,"mmm")

In Access, Dates are stored as a number, counting the number of days
from 12/31/1899.
So Format(12,"mmm") will set the value of the function to
January 11, 1900 and return the month of January.

So rather than getting 1, 2, 3... I would get JAN, FEB, MAR... But
for some reason it returns JAN regardless of what the number is.
If I run the query without the formatting I get an array of numbers
correctly representing the months: 1, 2, 3,...,11,12 The moment I
add the formatting code all I get is JAN down the column.

Any ideas on what might be causing this? It appears that access is
only picking up the 1 and ignoring anything else? I've run out of
ideas on how to get this to work. Any suggestions or workarounds
would be highly appreciated.

Thanks mucho! J

The answer is rather simple.
If you want a new column just format the date field:
MonthOfService:Format([DOCUMENT_DATE],"mmm")

The column will return a 3 letter month name.
If you wish the full month name, use "mmmm" in the format function.

Now if you already have a column of month numbers 1-12 and wish to
show the associated month name, if your access version supports it you
could use the MonthName() function.
MonthOfService:MonthName([NumberField])
3 = May, etc.

Hope this explains it for you.

 

[newbr]

Thanks so much fred! That explains a lot and at least I can now understand why I'm getting all the January months back. Also, I tried MonthOfService:MonthName([NumberField]) and it works like a charm. I really appreciate it

J