Discussion:
VB6 and Access 2003 date format
(too old to reply)
scbs29
2014-07-03 20:00:40 UTC
Permalink
Hello all
I don't know if it is a VB6 problem or Access 2003 problem or both or
if it is me.
I am writing a VB6 program as a front end to an Access 2003 database.
I know I could probably do it within Access but I prefer straight VB6.
Database access is through ADO using SQL.
When I insert data into the database I enter date formatted within VB6
as dd/mm/yyyy. Using a query to get data between 2 dates, then it
fails because Access treats datee as mm/dd/yyyy.
I have been searching but cannot seem to find any way of telling
Access to treate dates as dd/mm/yyyy.
It has been a few years, but I have a vague recollection of date
problems with VB6 accessing a SQL Server 2000 database, but I cannot
remember.
Am I missing something ?
Can anyone advise me?
--
remove fred before emailing
Registered Linux User 490858
John W. Vinson
2014-07-03 20:21:54 UTC
Permalink
Post by scbs29
Hello all
I don't know if it is a VB6 problem or Access 2003 problem or both or
if it is me.
I am writing a VB6 program as a front end to an Access 2003 database.
I know I could probably do it within Access but I prefer straight VB6.
Database access is through ADO using SQL.
When I insert data into the database I enter date formatted within VB6
as dd/mm/yyyy. Using a query to get data between 2 dates, then it
fails because Access treats datee as mm/dd/yyyy.
I have been searching but cannot seem to find any way of telling
Access to treate dates as dd/mm/yyyy.
It has been a few years, but I have a vague recollection of date
problems with VB6 accessing a SQL Server 2000 database, but I cannot
remember.
Am I missing something ?
Can anyone advise me?
Access dates are not stored with ANY format, or are they text strings. A
Date/Time value is stored as a Double Float number, a count of days and
fractions of a day (times) since midnight, December 30, 1899.

I'm sure what's happening is that you're inserting a formatted (dd/mm/yyyy)
date as a date literal - e.g. 05/11/2013 intending November 5. However, date
literals are always interpreted as mm/dd/yyyy if the date makes sense that
way; that is, 05/11/2013 will be stored as May 11. 16/05/2013 will be
"flipped" to May 16 because there is no 16th month.

The solution is to either roll with it and use American format, or the
unambiguous ISO format yyyy-mm-dd; or use the DateSerial function to
explicitly generate a date/time value.

Note that this Usenet newsgroup has been abandoned by Microsoft for several
years and gets very little traffic; only a few of us traditionalists still
even read it! You'll do better to use one of the forums in my .sig to get more
eyes on the problem.
--
John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:

http://answers.microsoft.com/en-us/office/forum/access?tab=question&status=all
http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev
and see also http://www.utteraccess.com
Loading...