Date Problem in VBA

[johnb]

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]

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]

Hi Dave

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

 

[johnb]

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