Date format problem

[patfee]

Hi,

I'm looping through a couple of recordsets to check if there are clashes. I compare the recordset date field against on other date.

the simplified explaination for this strange thing i'm expericiencing is as follows:

if rstDate > Date then .....

having rstDate = 13 / 1 / 2005 checked against Date = 15 / 1 / 2005 works fine (no "then" action initiated.

But, having rstDate = 1 / 5 / 2005 checked against Date = 15 / 1 / 2005 triggers the "then" action.

I understand that the problem has something to do with the data format. Case one access sees dd-mm-yyy while in case two, access sees mm-dd-yy

Could someone advise a solution on this please?

Thanks
Patrick

 

[Remou]

Hi
I think you must format the dates to mm/dd/yyyy:
Format (rstDate, "mm/dd/yyyy")

There are lots of posts about this, if you need a better explanation, for example thread701-890011

 

[LittleSmudge]

This is because JET ignores the International Setting on your PC and tries to interpret a date in the illogical mm/dd/yy format if it can, only reverting to the sneible dd/mm/yy format if the previous attempt fails.

This JET SQL bug must always be protected for whenever you work with dates in SQL strings.


The solution is something like :-

If Format(rst!Date, "Medium Date") = Format(Date(), "Medium Date") Then

Using "Medium Date" inserts LETTERS in the Month position so there can be no confusion at all.



'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[patfee]

thanks for this!! it definitely is the SQL buggy thing, because early february dates are checked wrongly as well against the january dates.

I still don't get it though....

The table were i'm getting the dates from are in formated in medium date so 01-Jan-2005.
opening the recordsets using a SQL string in VB converts it then back to mm-dd-yyy if i understand correctly?

So, if i have two recordsets created in VB both based on the same table, there should not be any problem with date formats i would say?

i.e.
tbl_1 = ID, Project, Resource, SDate <- date in medium format

rst1 is based on: "Select * from tbl_1 where Resource = 1;"
from this rst1 i fetch the Sdate (X = rst1.sdate everytime after i moved to the next record

rst2 is based on "select * from tbl_1 where Resource = 1 and Sdate > # " & X & "#;"

so the SQL mishap should work out the same for both record sets, thus comparing dates by mm-dd-yy no??

Thanks
Pat

 

[patfee]

oke solved.....

i formated the X = rst1.sdate to X = format (rst1.sdate, "mm/dd/yyy")

Cheers
PAT

 

[redapples]

Just a note, this is not a bug. The date format used is the American format. Hey, they wrote it they can use whatever they like. It could be worse, what's French for "If... Then... Else" or "Do Until... Loop" at least English speakers (mostly) like me only have things like dates to worry us, oh and "color"!!!

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk

 

[RoyVidar]

This can't be right...

As SkipVought would have said, a date is a date is a number...

meaning, a date is stored as a number when dumped to a date/time field, and when assigned to a variable of datatype date.

If you input the date May 10, 2005 into a table through for instance:

[tt]"update mytable set mydate = #2005-05-10#"[/tt]

Then the following should occur on three different locale after opening a recordset on the same table:

[tt]debug.print rs.fields("mydate").value, date

10.05.2005 23.06.2005 ' Norwegian
10/05/2005 23/06/2005 ' UK
5/10/2005 6/23/2005 ' US[/tt]

But - this is only the displayed date! What's "stored" in the field, and available through the date function are numbers - so - comparing real dates in code, regardless of whether they are variables of datatype date, or fields of datatype date/time will (at least far as I've found out) perform correct regardless of locale, because it compares numbers!!!

They are only viewed as a date whenever it's displayed. VB(A) works with the number.

Try (in immediate pane - ctrl+g):
[tt]? format$(date,"0")
? format$(38526, "yyyy-mm-dd")[/tt]

My suspicion with some of the above, is that there's some formatting going on, where real dates are converted to strings, reassigned either to date variables, variants or dates - but in the process implicitly "recreated/coerced/toggled" between US/UK dates. Remember that when using the format functtion, you are effectively formatting a date to a string, and if trying to assign a string to a variable of datatype date, an implicit conversion will be performed - the success of this may be what you're experiencing, cause this is a culprit for many until they get sufficient grasp on working with dates...

Either that, or you're assigning the date to a date variable using the wrong method. In VBA, when using litterals, they must be in US format, i e:

[tt]dim dtMyDate as date
dtMyDate = #1/5/2005# ' assigns 5. Jan 2005 regardless of locale
dtMyDate = #15/5/2005# ' assigns 15. May 2005 because
' 15 is higher than 12 (max month), so the date
' is coerced[/tt]

Which of course leads up to the following fun test, at least when you're not on US locale:

[tt]dtMyDate = #1/5/2005#
msgbox dtMyDate[/tt]

A nice trick, is using the DateSerial function, to make it completely clear what one is assigning

[tt]dtMyDate = dateserial(2005, 5, 1)[/tt]

The only time one would really need to format dates, is when performing dynamic SQL (as in my update sample), where, when concatenating the date into a string which is passed to the Jet engine for evaluation and execution, an unambiguous format is needed...

Also keep in mind that the "mm/dd/yyyy" format is far from safe, if formatting is needed, use "mm\/dd\/yyyy" or "yyyy-mm-dd", see International Dates in Access for more info.

Roy-Vidar