Grouped Month is not Accurate

  • Thread starter Thread starter Erik via AccessMonster.com
  • Start date Start date
E

Erik via AccessMonster.com

I have a query that groups daily entries monthly by the date (i.e. 1 for
January, 2 for February, etc). When I create the report the report
recognizes 1 as December instead of January, thus my data looks like it is
one month behind. Any ideas why?

Any help would be great.
 
Probably because you are doing something wrong.

Since we don't know what expression you are using to group your daily
entries, nobody can tell for sure.
 
GROUP BY [Errors by Month].GrpMonth

This groups data entry items by month. For January we had 5 entries. So the
query returns five entries for the month (1). However when I create the
report, 1 is recognized as December instead of January.
 
You probably are storing values 1-12 in the GrpMonth field. You are then
attempting to display the value 1 as a date which is actually Dec 31, 1899.
A value of 2 in GrpMonth is probably displaying like January.

If you have a value like 1 which represents the first month of the year and
want to display it as the month name then you can use
=Format(DateSerial(2000,[GrpMonth],1),"mmmm")
Depending on your version of Access, you could also use
=MonthName(GrpMonth)
 
I attempted to add this to the format properties of the text box. The
format automatically changes to "=For"m"at("d"ate"s"erial(2000,[Grp"m\on\
th"],1),mmmm)". Should this be updated on the query or report and where?
Sorry for beating this to a pulp.
 
What value is stored in GrpMonth? I made a guess at 1-12 but you haven't
confirmed this.
You probably have two options:
Set your
Control Source: =DateSerial(2000,GrpMonth,1)
Format: mmmm
or
Control Source: =MonthName(GrpMonth)
Format:

If you place an expression in the control source, make sure the name of the
control is not also a field name.
 
You are correct about the GrpMonth value being stored as 1 which is equal
to December 1899 and 2 equal to January. I attempted your scenarios but
neither one fixed the problem. The report reflects an error in the text
box. I am using Access 97 if that makes a difference.
 
Did you confirm my previous statement:
"If you place an expression in the control source, make sure the name of the
control is not also a field name."
 
Back
Top