The reason you cannot get the Table updated to "dd-mm-yyy" is that dates are
not stored as characters in formatted form -- they are stored as a number....
looks like a double precision floating point, but for dates, interpreted
with the whole number part representing date as number of days since Dec.
30, 1899 and the fractional part representing seconds since last midnight..
What is presented to you is that number with a format applied... either your
regional format, format in the table, format applied to the control on a
form, or a specific format used in a function. And many of us (I, for one)
have to review the precedence of the formats when we don't see what we
expect.
Unless someone has "slipped a change by me" in some recent version, if you
specify a date in a Query, you must use US date format, that is mmddyy or
mmddyyyy.
Larry .... Many thanks for responding.May I respond by addressing each
of your paragraphs.
Par. 1 I believe I understand
Par 2. I am using Allen Brownes ajbCalendar to populate an unbound
text box and it is NOT formatted. AfterUpdate the text box shows the
date as dd/mm/yyyy. There is no formatting in the SQL statement as
above and there is no formatting in the table. I have expanded my
TRIAL DB as follows:
strSQL = "INSERT INTO tblDates" _
& "(dtmDate1,dtmDate2, dtmDate3)" _
& "VALUES" _
& "(#" & Format(Me.dtmDate1, "dd/mm/yyyy") & "#, #" &
Me.dtmDate2 & "#" & ", " & CLng(dtmDate3) & ")"
Date 1 and Date2 both go into the table as mm/dd/yyyy and of course
Date3 goes in as an integer 41078 as it happens. Debug.Print as
follows
INSERT INTO tblDates(dtmDate1,dtmDate2, dtmDate3)VALUES(#04/06/2012#,
#11/06/2012#, 41078)
Again my system date formats are SHORT: dd/mm/yyyy and LONG: mmmm-dd-
yy
Does it make any sense to store all dates as integers (In this case I
am NOT dealing with the time part) knowing that you can more easily
format them as required on forms and reports?
Having said all of that there MUST be something else going on since I
have hundreds of records that have been saved in the same table that
are correct
Par 3: In your third paragraph, I think, you are suggesting that since
I am using SQL which is a query then I should be formatting it as mm/
dd/yyyy anyway. Okay but that is how it is being saved. I created a
BOUND test box to dtmDate2 and tried to format is as dd/mm/yyyy and
there was no change. If I format it as Shrort Date, Medium Date or
Long Date it does format to those. Date3 stores as integer can be
formatted any way I choose.
Still using OLDE access 2003. the only NEW thing is Win7
Thanks again for your response. I hope all this makes sense but
unfortunately I am no further ahead at the moment.
