Date and Time in MS Access 
Author Message
 Date and Time in MS Access

Hi Everyone,

I have written some code in Access that runs a query based on dates that
the user enters e.g. Start Date and End Date.

The problem is that some people that use the program have American Date
format (mm/dd/yyyy) and some have (dd/mm/yyyy). In one case, a query for
12 September 1997 (12/09/1997) turned out to be 9 December 1997
(09/12/1997).

I am letting the user enter the dates into a text box, then I'm taking
that text and putting it straight into the SQL statement with "#" either
side. Am I going about this the right way?

Does anyone have any suggestions?

Thanks,

Martin Shaw



Wed, 15 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access

 Martin,

using dates in SQL they HAVE to be in american format (mm/dd/yy). Using the
following command would deal with the different language versions/settings:
    Format(CDate(MyTextfield);"\#MM\/DD\/YYYY\#")

HTH,
Chris

--
Chris H. Tscheuschner
CINCOM Systems Germany
Client Services Department

DO NOT use reply - mailto: ctscheus(at)cincom(dot)com


Quote:
>Hi Everyone,

>I have written some code in Access that runs a query based on dates that
>the user enters e.g. Start Date and End Date.

>The problem is that some people that use the program have American Date
>format (mm/dd/yyyy) and some have (dd/mm/yyyy). In one case, a query for
>12 September 1997 (12/09/1997) turned out to be 9 December 1997
>(09/12/1997).

>I am letting the user enter the dates into a text box, then I'm taking
>that text and putting it straight into the SQL statement with "#" either
>side. Am I going about this the right way?

>Does anyone have any suggestions?

>Thanks,

>Martin Shaw




Wed, 15 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access

hi martin!
use function DateValue  (dont know if its available in your version. in
Access 8 it is). it converts nearly any text that looks like a date into
an sql-compatible format. (by the way: dont try to get help to this
keyword by contexthelp using F1. Winhlep will crash. Use index-tab of
help).
Hope that helps you.
Robert.



Wed, 15 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access



Quote:
> using dates in SQL they HAVE to be in american format (mm/dd/yy). Using the
> following command would deal with the different language versions/settings:
>     Format(CDate(MyTextfield);"\#MM\/DD\/YYYY\#")

I've lost count of the number of times I've seen this nonsense
posted. I can only think that Microsoft in their 'wisdom' have
stated this in the past. BUT it is WRONG. You do not HAVE to have
the date in a format that the rest of world (other than North
America) finds confusing.

Access, and SQL Srvr, Oracle and Sybase (and hopefully just about
every other DB Srvr), will happily accept the more meaningful date
string of

Format(YourDate, "dd mmm yyyy")  -  eg 27 Sep 1997

No ambiguity. Try it.
Ernest
--
ernest     at Noel Systems, near Kingston-upon-Thames (UK)
           (if replying by email, remove the * from address)



Wed, 15 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access

Hello,

You could use the CVDate function with your sql, something like this:-

Select * from MyTable Where MyDate = CVDate('" & txtRequiredDate &
"');"

Alan Davis Southampton, England



Thu, 16 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access

The way Which I have found to consistently always work is to convert the
date to Long Date Format before feeding it to the SQL statement.



Quote:
> Hi Everyone,

> I have written some code in Access that runs a query based on dates that
> the user enters e.g. Start Date and End Date.

> The problem is that some people that use the program have American Date
> format (mm/dd/yyyy) and some have (dd/mm/yyyy). In one case, a query for
> 12 September 1997 (12/09/1997) turned out to be 9 December 1997
> (09/12/1997).

> I am letting the user enter the dates into a text box, then I'm taking
> that text and putting it straight into the SQL statement with "#" either
> side. Am I going about this the right way?

> Does anyone have any suggestions?

> Thanks,

> Martin Shaw




Fri, 17 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access

Followup set to comp.databases.ms-access, comp.databases.ms-sqlserver

Ernest,

ever worked with multi-language applications? OK - Try your Access,
SQL-Server, Oracles, etc with the following:

5 March 1997 --> Format(CDate("01.05.1997"),"DD MMM YYYY") returns

with german settings:    05 Mrz 1997
with french settings:        05 mars 1997
with dutch settings:        05 mrt 1997
with italian settings:        05 mar 1997
with hungarian settings: 05 mrc 1997

or 1 January 1997
with german(Standard) settings:    01 Jan 1997
with german(Austrian) settings:    01 J?n 1997
......

Just a few variants - try them on an english DBServer - mine don't like
them....
Try them on a french, swedish or whatever version.....

Whereas Format(MyDate;"\#mm\/dd\/yyyy\#") works on all that I know
(including a whole bunch of Mainframe-DBMSs)

And PLEASE, tell the results of what you can test - maybe I've made my live
too complicated in the past.....

Chris

--
Chris H. Tscheuschner
CINCOM Systems Germany
Client Services Department

DO NOT use reply - mailto: ctscheus(at)cincom(dot)com


Quote:


>> using dates in SQL they HAVE to be in american format (mm/dd/yy). Using
the
>> following command would deal with the different language
versions/settings:
>>     Format(CDate(MyTextfield);"\#MM\/DD\/YYYY\#")

>I've lost count of the number of times I've seen this nonsense
>posted. I can only think that Microsoft in their 'wisdom' have
>stated this in the past. BUT it is WRONG. You do not HAVE to have
>the date in a format that the rest of world (other than North
>America) finds confusing.

>Access, and SQL Srvr, Oracle and Sybase (and hopefully just about
>every other DB Srvr), will happily accept the more meaningful date
>string of

>Format(YourDate, "dd mmm yyyy")  -  eg 27 Sep 1997

>No ambiguity. Try it.
>Ernest
>--
>ernest     at Noel Systems, near Kingston-upon-Thames (UK)
>           (if replying by email, remove the * from address)



Fri, 17 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access

Quote:

> hi martin!
> use function DateValue  (dont know if its available in your version. in
> Access 8 it is). it converts nearly any text that looks like a date into
> an sql-compatible format. (by the way: dont try to get help to this
> keyword by contexthelp using F1. Winhlep will crash. Use index-tab of
> help).
> Hope that helps you.
> Robert.


No!! *Don't* use DateValue for this! It's been available since
Access 2.0, but its output obeys the regional settings, exactly
what you *don't* want when formatting dates for SQL! Use this:

Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\U\L\L"
SQL = "select foo from bar where baz >= " & Format$(MyDate, JetDateTimeFmt)

There may be a way to get Access to like dates in yyyy-mm-dd
format, which is comprehensible to most of the world, but I
haven't found it yet. :-( :-( :-(

--

WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take CJ3 away, ha ha!



Fri, 17 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access



 > > using dates in SQL they HAVE to be in american format (mm/dd/yy). Using the
 > > following command would deal with the different language versions/settings:
 > >     Format(CDate(MyTextfield);"\#MM\/DD\/YYYY\#")

 > I've lost count of the number of times I've seen this nonsense
 > posted. I can only think that Microsoft in their 'wisdom' have
 > stated this in the past. BUT it is WRONG. You do not HAVE to have
 > the date in a format that the rest of world (other than North
 > America) finds confusing.

 > Access, and SQL Srvr, Oracle and Sybase (and hopefully just about
 > every other DB Srvr), will happily accept the more meaningful date
 > string of

 > Format(YourDate, "dd mmm yyyy")  -  eg 27 Sep 1997

 > No ambiguity. Try it.
 > Ernest

Maybe no ambiguity, but there'll be plenty of errors if you set
your language settings to just about anything besides English.

We may be stuck with the USA format for dates embedded in
strings, since I've been unable to find a yyyy-mm-dd format that
doesn't {*filter*}CDate, CVDate, and/or Jet. :-( :-( :-(

--

WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take CJ3 away, ha ha!



Fri, 17 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access

Unfortunately the world outside the US has problems with Access being too
clever with dates. . . .



Quote:
> hi martin!
> use function DateValue  (dont know if its available in your version. in
> Access 8 it is). it converts nearly any text that looks like a date into
> an sql-compatible format. (by the way: dont try to get help to this
> keyword by contexthelp using F1. Winhlep will crash. Use index-tab of
> help).
> Hope that helps you.
> Robert.




Fri, 17 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access

hi,
im from Germany, and i didnt have any problems with dateValue. It seems
to me that this function orientates on the windows system settings. Also
my customers dont have problems with it.
Robert



Sat, 18 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access



The ORIGINAL posting related to problems with access not coping with
the date being sent, and one answer was that the date HAD to be
mm/dd/yyyy. I know that the date doesn't HAVE to be that. Hence my
response. You NOW introduce multi-language issues.

Quote:
> ever worked with multi-language applications? OK - Try your Access,
> SQL-Server, Oracles, etc with the following:

Yup, in fact for an American international co - but as data has to
be passed between SQL Srvr databases in over 10 different European
countries, all the servers have been setup for English. Also have
a Swiss customer who use Access database.

Quote:
> with french settings:        05 mars 1997
> with dutch settings:        05 mrt 1997
> with italian settings:        05 mar 1997
> with hungarian settings: 05 mrc 1997

what about all the 4 swiss ones  :)

Quote:
> Just a few variants - try them on an english DBServer - mine don't like
> them....
> Try them on a french, swedish or whatever version.....

> Whereas Format(MyDate;"\#mm\/dd\/yyyy\#") works on all that I know
> (including a whole bunch of Mainframe-DBMSs)

I totally accept that this multi-language issue is a problem. But
I am somewhat confused. I know that our approach works in several
different European countries. The sw is produced using an English
ver of VB3. Localised with Wippleware. This resulting sw is then
installed on the pc's in other countries. This works, so is this
because the program has always been produced using English VB?

Cheers
Ernest
--
ernest     at Noel Systems, near Kingston-upon-Thames (UK)
           (if replying by email, remove the * from address)



Mon, 20 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access
Quote:

> Hello,

> You could use the CVDate function with your sql, something like this:-

> Select * from MyTable Where MyDate = CVDate('" & txtRequiredDate &
> "');"

> Alan Davis Southampton, England



Mon, 20 Mar 2000 03:00:00 GMT  
 Date and Time in MS Access

I set the critera for a select query to refrence the control on a form,
but if the control includes < or >, a "Can't evaluate expression" error
is returned.

I've managed to get around this with the use of the Between statement,
but it fails when used as criteria for a calculated field in the query.
PLEASE HELP!!!!



Mon, 20 Mar 2000 03:00:00 GMT  
 
 [ 19 post ]  Go to page: [1] [2]

 Relevant Pages 

1. ADO with Access Date/Time field not storing the time, just the date

2. Insert date and time field into MS Access with SQL

3. CRW with MS Access 2.0 date/time fields

4. MS Access Date/Time Field in Crystal

5. MS Access Date/Time field - American/English

6. Compare date in form with date/time in form with date/time in database

7. MS-ACCESS query date problem + date formatting in datagrid

8. ms-access - delete old dates and change dates

9. How do I store an empty date on a date field (MS Access)

10. Null date values in Date/Time fields in Access DB

11. Matching system date/time with field date/time

12. combining a time and a date to one Date/Time value

 

 
Powered by phpBB® Forum Software