Usa/Eur date conversion? 
Author Message
 Usa/Eur date conversion?

Hello

I'm using a CreateQueryDef to make a 'dynamic' query which searches for
records.
Users can enter their searchparameters in a form, hit a button, and the
vbcode assembles a Query with those parameters.

Like this:

    Dim datDateFrom as Date
    Dim DateTo as Date
    ......
    Set qrySearchResults = dbWissink.CreateQueryDef("qrySearchResults",
"SELECT " & _
    "Administratie_correspondentie.* " & _
    "FROM Administratie_correspondentie " & _
    "WHERE (((Administratie_correspondentie.Date) >= #" & datDateFrom &
"#) " & _
    "AND (((Administratie_correspondentie.Datum) <= #" & datDateTo &
"#);")

The values of datDateFrom and datDateTo are '7-11-99' (I checked it in
he running code, using debug), but when I take a look at the Design of
the Query created, they've  changed to '11-7-99'.

Does anybody know how this happens, and more important, how do I prevent
it from happening :-)

------Tnx, /\/\uttley



Thu, 27 Dec 2001 03:00:00 GMT  
 Usa/Eur date conversion?

says...

Quote:
>     "WHERE (((Administratie_correspondentie.Date) >= #" & datDateFrom &
> "#) " & _

Here, you're converting the date value into a string.  Where you're going
wrong is that you're not specifying what date format to use, so Access
chooses the Short Date format from the Control Panel.

To fix it, make the formatting explicit.  And please, use an
*unambiguous* format.  E.g.:  Format$(datDateForm, "dd-mmm-yyyy")

That way, you don't have to worry about the ridiculous switching of
months and days.

Here are some other posts & rants on the subject:
http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=474544830
http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=449531092
http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=454317680

--
Paul Bredbury, http://dialspace.dial.pipex.com/brebs/



Thu, 27 Dec 2001 03:00:00 GMT  
 Usa/Eur date conversion?

Quote:

>To fix it, make the formatting explicit.  And please, use an
>*unambiguous* format.  E.g.:  Format$(datDateForm, "dd-mmm-yyyy")

I'm not clear how Jet responds to non-English language dates: the
following are safer:

  "#" & Format$(datDateForm, "mm/dd/yyyy") & "#"
  "#" & Format$(datDateForm, "yyyy-mm-dd") & "#"

Tim F

--



Thu, 27 Dec 2001 03:00:00 GMT  
 Usa/Eur date conversion?
Hi,
  I had the same problem in finding dates changed around. The jet engine
uses the mm/dd/yy format.
The way I fixed this problem is to explicitely tell the system the format to
use:
The query (use BETWEEN instead of <= and >= ..)

...
WHERE Administratie_correspondentie.Date BETWEEN #" _
& Format(datDateFrom, "mm/dd/yy") & "# AND #" _
& Format(datDateFrom, "mm/dd/yy") & "#"



Sat, 29 Dec 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Change Default Date Format, USA -> UK

2. VB 5 and 6 release dates in USA

3. ACC97:Date Conversion - Date to Double

4. YYYYMMDD string date conversion

5. Access 97 date conversions

6. Date Conversion

7. date conversion functions

8. date conversion function

9. julian date conversion

10. code for a really bizarre date conversion

11. Date conversion

12. Julian date conversion

 

 
Powered by phpBB® Forum Software