Access Linked to Excel Producing Wrong Month

  • Thread starter Thread starter PatK
  • Start date Start date
P

PatK

Hi!

I have an Access 2007 Table that I Have linked to a pivot inside of Excel
2007. I want to be able to pivot on a date field, and have identified the
field with the date, when I set up the pivot. However, what is showing up in
the pivot is ONLY the month (eg, NOV), instead of the full date. WHat is
then happening, as my access DB contains rows with dates from several years,
is that all the NOV's are being added together, regardless of year.

Why is it doing this be default? How can I undo this? WHat I really want
is YYYY-MM or MM-YYYY format in my pivot (I do not need to get to date level
granularity).

Thanks!

PatK
 
The internal representation of a date is independent of the FORMAT you use,
like 12, internally, is represented with 0 and 1 (in binary), not as you SEE
it. So, if your data is really a date (date_time) value, use the format you
want:

In the immediate (debug) window:

? Format( now, "mm-yyyy"), Format(now, "yyyy-mm"), Format( now, "mmmyy")
12-2007 2007-12 Dec07



So, use the FORMAT you want to see as name for the fields that will be
created. Can be

.....
PIVOT( dateTimeFieldName, "mmmyyyy" )




Hoping it may help,
Vanderghast, Access MVP
 
This was actually information in a pivot table, in excel, and not in VBA
code, so I did not have the flexibility of controlling it, which is what we
were trying to accomplish. However (!), I think we figured it out. THe
spreadsheet we were using came from another individual, and we chatted with
them, it turns out they were using "grouping" of data in excel, and it did
not differentiate the "year" when you group on date (very strange). Thus, it
grouped nov data from every year, together, when it summarized it. We
figured out how to further subgroup the data by Month/Day, as well, and it
worked. Very odd, tho...I would have thought the default grouping would have
been on the entire data field, not just the month. Very strange....anyway,
thought I would post this, lest someone else run into this.

Thanks!

Patk
 
Back
Top