MastercafeCTV
mastercafe at ctv.es
Wed Dec 8 21:09:01 CST 2004
We check this many time and this is not the problem. Actually we use Spanish format to introduce all date dd/mm/yyyy and when we need a SQL filter try to put Format(field;'yyyymmdd') to void the problem. Look this: Field 1 Field 2 Field 3 Cname1 01/03/2001 Some data Cname2 01/04/2001 Some data Cname3 01/05/2001 Some data Cname4 01/06/2001 Some data Cname5 01/07/2001 Some data Cname6 01/08/2001 Some data Cname7 01/09/2001 Some data Cname8 01/10/2001 Some data Cname9 01/11/2001 Some data If we try to Select * from DDBB where field2>=#01/06/2001# Normally you must be obtain Cname4 to 9, but we obtain all (01 is month) If we try to Select * from DDBB where field2>=#13/06/2001# Normally you must be obtain Cname5 to 9 in this case run perfect (13 is day) Then we change our SQl to Select * from DDBB where format(field2,'yyyymmdd')>='20010601' you must be obtain Cname4 to 9 and in this case run perfect again. Why sometimes change Day with Month?.. Not in Control Panel, not in Access Options... Where?? And why? A feel stupid with this yet. Thanks Juan -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Borge Hansen Sent: jueves, 09 de diciembre de 2004 03:40 To: Access Developers discussion and problem solving Subject: Re: [AccessD] Try and customize the settings of languages, numbers, times and dates. You probably have your machine set to a US English format.... On WinXP go Control Panel => Regional and Language Options. This should clear up the problem you are describing. Always use US date format when you are using a Date as a selection criterium, etc. in SQL action queries. Hope this helps Borge ----- Original Message ----- From: "MastercafeCTV" <mastercafe at ctv.es> To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> Sent: Thursday, December 09, 2004 9:25 AM Subject: [AccessD] > > Hi group, this week we are looking for DATE problems and solutions > over this > bug from Access. > > We use normally dd/mm/yyyy to capture de date information, and > configure the > windows DATE/TIME to this format, when yo try to input some date you > can see > this diferences: > > Input Format Format dd/mmm/yy > 01/02/2001 - 01 Feb 01 > 03/02/2001 - 02 Mar 01 > 13/02/2001 - 13 Feb 01 > 03/04/2001 - 03 Apr 01 > > Its totally stupid the translation, we saw that use the main format as they > want. If you put a day over >12 then the format it's OK dd/mm/yyyy, > but if you put a day under 13 depend of the month.. If the month if > higher then the > format that the use is mm/dd/yyyy but not always, only for months > lower than > 6. > > At this moment we only found a solution for this, convert all date to string > 8 positions (yyyymmdd) and then use this ISO format to make any SQL, > but the > speed are not good on a large ammount of registry. > > Any ideas to control de DATE correctly?? We problem we saw in Access 2 > and always use String format, but actually we hope that with Access XP > and WinXP > could be solved. > > Thanks. > > > Juan Menendez > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com --- Outgoing mail is certified Virus Free by AVG Anti Virus System. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.799 / Virus Database: 543 - Release Date: 20/11/2004 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com