Access/VB Date Format Problem

My settings are correct in Control Panel (dd/mm/yyyy)

laurent wrote:

 > I have "fix" the same problem few minutes late modifiing the Regional
 > setting in the windows Control panel.
 >
 > David Peake wrote:
 >
  > > This is really bugging me!
  > >
  > > I have a table that audits useage (date, time & user etc).
  > >
  > > Every time a user changes a record it modify the record to say what
  > > date it was last modified. Easy so far:
  > >
  > > SQL="Update TBLCustomer SET ModifyDate=#" & format(date(),
  > > "dd/mm/yyyy") & "#"
  > > cnn.Execute SQL
  > >
  > > (I'm in the UK so I want the date to be in dd/mm/yyyy format)
  > >
  > > However, when i retrive the dates (into a list view) it returns
  > > them in USA format when the day value is <= 12, however, if it is >
  > > 12 it format correctly. I've tried using format(rs("ModifyDate"),
  > > "dd/mm/yyyy") but this doesn't work.
  > >
  > > eg, 07 June 2004 would return: 06/07/2004 (6 July 2004 in the UK)
  > > 20 May 2004 would return 20/05/2004 (this is OK)
  > >
  > > Any ideas, or r i being thick?
  > >
  > > Cheers
  > >
  > > Dave
 >> Stay informed about: Access/VB Date Format Problem 

On Mon, 07 Jun 2004 15:33:19 +0100, "David Peake"
wrote:

 >My settings are correct in Control Panel (dd/mm/yyyy)
 >
 >laurent wrote:
 >
  >> I have "fix" the same problem few minutes late modifiing the Regional
  >> setting in the windows Control panel.
  >>
  >> David Peake wrote:
  >>
   >> > This is really bugging me!
   >> >
   >> > I have a table that audits useage (date, time & user etc).
   >> >
   >> > Every time a user changes a record it modify the record to say what
   >> > date it was last modified. Easy so far:
   >> >
   >> > SQL="Update TBLCustomer SET ModifyDate=#" & format(date(),
   >> > "dd/mm/yyyy") & "#"
   >> > cnn.Execute SQL
   >> >
   >> > (I'm in the UK so I want the date to be in dd/mm/yyyy format)
   >> >
   >> > However, when i retrive the dates (into a list view) it returns
   >> > them in USA format when the day value is <= 12, however, if it is >
   >> > 12 it format correctly. I've tried using format(rs("ModifyDate"),
   >> > "dd/mm/yyyy") but this doesn't work.

You could try setting the data type of the list view and make it TEXT
- but you would need to provide a custom sort routine as well if you
supply that functionality to the user.

Maybe you should try to internationalize and use a less ambiguous
format like 2004-Jun-7 instead, while you're at it?

   >> > eg, 07 June 2004 would return: 06/07/2004 (6 July 2004 in the UK)
   >> > 20 May 2004 would return 20/05/2004 (this is OK)

- snip -
_______________________
Michael B. Johnson
 >> Stay informed about: Access/VB Date Format Problem 

"David Peake" wrote in message

 > This is really bugging me!
 >
 > I have a table that audits useage (date, time & user etc).
 >
 > Every time a user changes a record it modify the record to say what
 > date it was last modified. Easy so far:
 >
 > SQL="Update TBLCustomer SET ModifyDate=#" & format(date(),
 > "dd/mm/yyyy") & "#"
 > cnn.Execute SQL
 >
 > (I'm in the UK so I want the date to be in dd/mm/yyyy format)
 >

A date in an ADO database is not in any format. It is an 8 byte floating
point value (days, with fractional days for time). Once you get the
correct date in, you can then display it in any format you like.

When the above executes on 07 June 2004, the date is converted to a
string: "#07/06/2004#", per your instructions. To Access, this is the
7th day of July 2004.

A well buried quote from the Access Help File:
"When you specify the criteria argument, date literals must be in U.S.
format, even if you're not using the U.S. version of the Microsoft Jet
database engine. For example, May 10, 1996, is written 10/5/96 in the
United Kingdom and 5/10/96 in the United States."

You could use the "universal" format to get the date in: "yyyy/mm/dd",
i.e. 2004/06/07. This is more portable between different database
systems. You can always display the data using whatever format you like.

Or, don't pass the date as a string at all, but instead use a command
object, and add a parameter of type adDate.
 >> Stay informed about: Access/VB Date Format Problem 

I'd love to use the international format of yyyy-mm-dd, as this would
solve loads of problems (including listview sorting) but users demand
that it's in a format that they understand easily.

It'd be a breeze if i didn't have any users

Dave


Michael B. Johnson wrote:

 > On Mon, 07 Jun 2004 15:33:19 +0100, "David Peake"

 >
  > > My settings are correct in Control Panel (dd/mm/yyyy)
  > >
  > > laurent wrote:
  > >
   > >> I have "fix" the same problem few minutes late modifiing the
 > Regional >> setting in the windows Control panel.
   > >>
   > >> David Peake wrote:
   > >>
   > >> > This is really bugging me!
   > >> >
   > >> > I have a table that audits useage (date, time & user etc).
   > >> >
   > >> > Every time a user changes a record it modify the record to say
 > what >> > date it was last modified. Easy so far:
   > >> >
   > >> > SQL="Update TBLCustomer SET ModifyDate=#" & format(date(),
   > >> > "dd/mm/yyyy") & "#"
   > >> > cnn.Execute SQL
   > >> >
   > >> > (I'm in the UK so I want the date to be in dd/mm/yyyy format)
   > >> >
   > >> > However, when i retrive the dates (into a list view) it returns
   > >> > them in USA format when the day value is <= 12, however, if it
 > is > >> > 12 it format correctly. I've tried using
 > format(rs("ModifyDate"), >> > "dd/mm/yyyy") but this doesn't work.
 >
 > You could try setting the data type of the list view and make it TEXT
 > - but you would need to provide a custom sort routine as well if you
 > supply that functionality to the user.
 >
 > Maybe you should try to internationalize and use a less ambiguous
 > format like 2004-Jun-7 instead, while you're at it?
 >
   > >> > eg, 07 June 2004 would return: 06/07/2004 (6 July 2004 in the UK)
   > >> > 20 May 2004 would return 20/05/2004 (this is OK)
 >
 > - snip -
 > _______________________
 > Michael B. Johnson
 >> Stay informed about: Access/VB Date Format Problem