Grouped Month is not Accurate

  • Thread starter Erik via AccessMonster.com
  • 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.
 
D

Duane Hookom

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.
 
E

Erik via AccessMonster.com

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.
 
D

Duane Hookom

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)
 
E

Erik Mineishi via AccessMonster.com

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.
 
D

Duane Hookom

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.
 
E

Erik Mineishi via AccessMonster.com

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.
 
D

Duane Hookom

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