date formatting troubles

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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])
Click to expand...

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")
Click to expand...

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
Click to expand...

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.
 
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
 
Back
Top