Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem
"Gassy" wrote
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem
Thanks a lot Frank - it works a treat.
Headache gone Cheers Mark "Frank_Hamersley" wrote: "Gassy" wrote 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem
"Frank_Hamersley" wrote ...
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. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem
"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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem
"Frank_Hamersley" wrote ...
"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 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 above (including North America <g). However, for peace of mind using an unambiguous date format can be good strStartDate = Format$(datStartDate, "dd mmm yyyy") 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date problem | New Users to Excel | |||
Exel increment date problem wrt todays date. | Excel Worksheet Functions | |||
Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
DATE PROBLEM | Excel Worksheet Functions | |||
Date problem | Excel Programming |