[AccessD] Date Problem

On 9 Dec 2004 at 4:09, MastercafeCTV wrote:

> 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
> 
OK -  the above are displayed in your short date format of dd/mm/yy

> 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)

SQL "Select ...... #...#"  assumes a US date format mm/dd/yyyy if valid so 
it selects everything greater than 6 Jan 2001.  

> 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)

SInce 13 is not a valid month, the SQL falls back to the second *possible* 
(see below re 2 digit years)  interpretation and uses 13 Jun 2001 instead. 

> 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.

That's logical.

> Why sometimes change Day with Month?.. Not in Control Panel, not in Access
> Options... Where?? And why? A feel stupid with this yet.
> 
It only changes from the standard US mm/dd/yyyy (or mm/dd/yy) where that 
interpretation does not return a valid date.  Note that if you use a two 
digit year, it tries  yy/mm/dd as the second interpretation before getting 
round to trying dd/mm/yy  so 13/06/01 is likely to end up interpreted 1 Jun 
2013 :-( 


One solution is to use an  unambigous format for comparison such as 

Select * from DDBB where  field2 > "1 Jun 2001"

If pulling the comparison from a text box etc, I always use:
Select * from DDBB where field2 > Datevalue('" & txtTextBox & "'")
That way, it doesn't matter how the the text box and regional settings are 
configured, it always does the comparison using the underlying value of the 
control/field.



-- 
Stuart