Dates in an Acess DB

[dpaulson]

I have a app that I've written in VB6 which uses an access db.
In a table in the database I have a field as a date/time. When looking at this field with visdata it looks like is stored in a m/d/y format for the first 12 days of the month and d/m/y format for the rest of the month. What do I need to do to correct this?


David Paulson

 

[CaptainD]

I don't think you have to do anything to it. Access stored Date and Time fields in one way. 000011.0010234 (or something like that, you'll need to look it up to be more precise) the digits on the left represent the date, the numbers to the right of the decimal represent the time.

When you pull the data from the table, you can then format it to however you want. (There are different standards depending on where you are at (Europe, US etc.))

Run a query, place it in a form and format it to show the way you want. Format(rs.dDateReturned, "MM/DD/YYYY")

 

[dpaulson]

It seems if I format the value (yyyy-MM-dd) when I write to the database, it does work correctly. If I format the value to vbshortdate, it does not. So problem is solved.

David Paulson

 

[simonkue]

David,

Have you tried converting the dates to Doubles. I had major problems creating SQL from VB and submitting dates that can be transposed into Access until using the conversion.

 

[Golom]

You are probably using a "dd/mm/yyyy" convention. Note that Access internally uses "mm/dd/yyyy" if the date specified is ambiguous (e.g. 03/05/2006 will be construed as "March 5, 2006" and NOT as "April 3, 2006")

Any of these formats will work correctly
- 03-Apr-2006
- 2006-05-03
- Apr-03-2006

But these are ambiguous
[tt]
- 03/05/2006 Translates to March 5 NOT May 3
- 07/11/2006 Translates to July 11 NOT Nov 7
- 10/07/2006 Translates to October 7 NOT Jul 10
[/tt]
Internally Access stores a Date as a Double. There's really nothing that you can do to change it's internal representation.