[AccessD] Date Problem

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




More information about the AccessD mailing list