Date Problem

[Guest]

I am writing some code in an Excel workbook that retrieves data from an
Access database. The SQL for the recordset is generated within the code and
is dependant upon two dates to define the period for which I want the data
returned
Code is thus (simplified)

Dim datStartDate as Date
Dim datEndDate as Date
Dim strSQL as String

datStartDate=DateSerial(Year(Date),Month(Date)-1,1) 'First of last month
datEndDate=DateSerial(Year(Date),Month(Date),0) 'Last day of last month

'I live in the UK so the date is returned as dd/mm/yyyy, but i need it
returned in
'the US format to apply to the SQL string, so

datStartDate=Format(datStartDate,"mm/dd/yyyy")
datEndDate=Format(datEndDate,"mm/dd/yyyy")

strSQL="SELECT SUM(ACTS) AS A FROM STATS WHERE ACT_DATE BETWEEN " _
& "#" & datStartDate & "# AND #" & datEndDate & "#"

Should work perfectly? No!
datStartDate returns 08/01/2004 (Date in US format)
But datEndDate returns 31/08/2004 (Not in US Format)

I have tried changing the date on my PC but the end date keeps being
returned in UK format and therefore being passed into the SQL string in this
manner. This is driving me nuts. Can anyone help.
TIA
Mark
Mark

 

[Frank_Hamersley]

Dim datStartDate as Date
Dim datEndDate as Date
Dim strSQL as String

datStartDate=DateSerial(Year(Date),Month(Date)-1,1) 'First of last month
datEndDate=DateSerial(Year(Date),Month(Date),0) 'Last day of last month

datStartDate=Format(datStartDate,"mm/dd/yyyy")
datEndDate=Format(datEndDate,"mm/dd/yyyy")

Format statement is useless here because you are reassigning back to the
date type variables - vb will cast your strings without warning using its
good ol' NA wonky behaviours. I suggest you Dim some str variables and
change the SQL to match eg.

strSQL="SELECT SUM(ACTS) AS A FROM STATS WHERE ACT_DATE BETWEEN " _
& "#" & strStartDate & "# AND #" & strEndDate & "#"

BTW there are alot more quirks regarding date handling that I would care to
go into suffice to say do not rely on the IsDate() function when looking to
convert strings to dates - you need to write your own to be safe in a non
"mm/dd/yyyy" locale.

Cheers, Frank.

 

[Guest]

Thanks a lot Frank - it works a treat.
Headache gone

Cheers

Mark

 

[Jamie Collins]

there are alot more quirks regarding date handling that I would care to
go into suffice to say do not rely on the IsDate() function when looking to
convert strings to dates - you need to write your own to be safe in a non
"mm/dd/yyyy" locale.

From testing I've conluded that seems Jet 4.0 gives entirely
consistent results using ISO-8601 standard date format, being
yyyy-mm-dd e.g. in VBA:

strStartDate = Format$(datStartDate, "yyyy-mm-dd")

However, for peace of mind using an unambiguous date format can be
good e.g.

strStartDate = Format$(datStartDate, "dd mmm yyyy")

Jamie.

--

 

[Frank_Hamersley]

"Jamie Collins" wrote

From testing I've conluded that seems Jet 4.0 gives entirely
consistent results using ISO-8601 standard date format, being
yyyy-mm-dd e.g. in VBA:

I should qualify my comment in saying it springs from VBA Excel and the date
formats of "yyyy.mm.dd" which I generally consider as European (esp with the
period sep char) and Asian (well mainland China at least), "mm/dd/yyyy" as
North American and "dd/mm/yyyy" for Commonwealth countries (generally).
When parsing string typed inputs some functions like Isdate() give kooky
results and then assigning the value to a spreadsheet cell without care can
lead to some unexpected casting/conversion. Once a genuine date datatype is
in use then things happen as you would expect.

However, for peace of mind using an unambiguous date format can be good e.g.

strStartDate = Format$(datStartDate, "dd mmm yyyy")

I agree 100% - especially when crossing over to different vendors products!

Cheers Frank.

 

[Jamie Collins]

"Jamie Collins" wrote
I should qualify my comment in saying it springs from VBA Excel and the date
formats of "yyyy.mm.dd" which I generally consider as European (esp with the
period sep char) and Asian (well mainland China at least), "mm/dd/yyyy" as
North American and "dd/mm/yyyy" for Commonwealth countries (generally).

And ISO-8601 is the international standard which should cover all the

I agree 100% - especially when crossing over to different vendors products!

There could be problems with differing regional settings on the client
and server machines e.g. '01 MAI 2004' produced on a French language
machine will not be recognized as a date on an English language
machine.

Jamie.

--