Hi All
I'm importing data from an Access query in to an Excel pivot table and as I
many of these imports to do I set the following constants in a standatd
module.
Private Const YearStart As Date = #1/4/2007#
Private Const YearEnd As Date = #31/3/2008#

The Dates I need are 1st April 2007 to 31st March 2008 however the date
#1/4/2007# is changed by Excel to #4/1/2007# so that the pivottable show for
the 1st january 2007. I've chacked the Windows XP Regional setting for the
date and are dd/mm/yyyy which is ok. Is there another setting somewhere? Any
suggesstions as to why the date #1/4/2007# is being changed?

TIA
johnb

VBA is pretty USA centric.

I'd try the USA settings: #4/1/2007# (for April 1, 2007)

Personally, I'd try to remove any ambiguity:

Private YearStart as Date
....

Later
yearstart = dateserial(2007,4,1)

===
I have no idea how Access queries work, though.

johnb wrote:
>
> Hi All
> I'm importing data from an Access query in to an Excel pivot table and as I
> many of these imports to do I set the following constants in a standatd
> module.
> Private Const YearStart As Date = #1/4/2007#
> Private Const YearEnd As Date = #31/3/2008#
>
> The Dates I need are 1st April 2007 to 31st March 2008 however the date
> #1/4/2007# is changed by Excel to #4/1/2007# so that the pivottable show for
> the 1st january 2007. I've chacked the Windows XP Regional setting for the
> date and are dd/mm/yyyy which is ok. Is there another setting somewhere? Any
> suggesstions as to why the date #1/4/2007# is being changed?
>
> TIA
> johnb

--

Dave Peterson

Hi Dave

Yeah. good point to define it later I shall try it!
regards
johnb

"Dave Peterson" wrote:

> VBA is pretty USA centric.
>
> I'd try the USA settings: #4/1/2007# (for April 1, 2007)
>
> Personally, I'd try to remove any ambiguity:
>
> Private YearStart as Date
> ....
>
> Later
> yearstart = dateserial(2007,4,1)
>
> ===
> I have no idea how Access queries work, though.
>
> johnb wrote:
> >
> > Hi All
> > I'm importing data from an Access query in to an Excel pivot table and as I
> > many of these imports to do I set the following constants in a standatd
> > module.
> > Private Const YearStart As Date = #1/4/2007#
> > Private Const YearEnd As Date = #31/3/2008#
> >
> > The Dates I need are 1st April 2007 to 31st March 2008 however the date
> > #1/4/2007# is changed by Excel to #4/1/2007# so that the pivottable show for
> > the 1st january 2007. I've chacked the Windows XP Regional setting for the
> > date and are dd/mm/yyyy which is ok. Is there another setting somewhere? Any
> > suggesstions as to why the date #1/4/2007# is being changed?
> >
> > TIA
> > johnb
>
> --
>
> Dave Peterson
>

I'm using a UK date format eg dd/mm/yyyy and I use a Yearstart as a Const coz
it's referred to many time in the module. So I need to force a UK date format
and my brains on strike. Ant ideas?
regards

johnb

"johnb" wrote:

> Hi Dave
>
> Yeah. good point to define it later I shall try it!
> regards
> johnb
>
> "Dave Peterson" wrote:
>
> > VBA is pretty USA centric.
> >
> > I'd try the USA settings: #4/1/2007# (for April 1, 2007)
> >
> > Personally, I'd try to remove any ambiguity:
> >
> > Private YearStart as Date
> > ....
> >
> > Later
> > yearstart = dateserial(2007,4,1)
> >
> > ===
> > I have no idea how Access queries work, though.
> >
> > johnb wrote:
> > >
> > > Hi All
> > > I'm importing data from an Access query in to an Excel pivot table and as I
> > > many of these imports to do I set the following constants in a standatd
> > > module.
> > > Private Const YearStart As Date = #1/4/2007#
> > > Private Const YearEnd As Date = #31/3/2008#
> > >
> > > The Dates I need are 1st April 2007 to 31st March 2008 however the date
> > > #1/4/2007# is changed by Excel to #4/1/2007# so that the pivottable show for
> > > the 1st january 2007. I've chacked the Windows XP Regional setting for the
> > > date and are dd/mm/yyyy which is ok. Is there another setting somewhere? Any
> > > suggesstions as to why the date #1/4/2007# is being changed?
> > >
> > > TIA
> > > johnb
> >
> > --
> >
> > Dave Peterson
> >