Stumped By Dates

  • Thread starter Thread starter TeeSee
  • Start date Start date
T

TeeSee

Access 2003, WIN 7, Regional settings= English, Canada

No matter what I try formatting date field in tables, formatting or
not during insert to tables using strDate = "#" & mydate & "#" Icannot
get the table updated to dd-mm-yyy. The following is a trial SQL print
out that looks correct and Insert to the table okay but in mm/dd/yyyy
format

INSERT INTO tblDates(dtmDate1,dtmDate2)VALUES(#04/06/2012#,
#11/06/2012#) .... The dates are the fourth and eleventh of June 2012.
When inserted to the tbl they show as 06/04/2012 etc.
This seems to have started when I changed from XP to Win 7
Could someone please advise how to correct.

Thank you
 
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.
 
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.
Click to expand...

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.
 
If you are going to perform calculations on, or going to manipulate, a
date/time value, it makes sense to store it as Date/Time data type. If you
are just keeping it for the user to view, especially if you want it in a
particular format, it makes sense to store it the way you want to display
(e.g., preformatted into text).
Still using OLDE access 2003. the only NEW thing is Win7
Click to expand...

Nothing wrong with that. Some of us just consider that Access 2003 is "the
latest version of Classic Access" (before the user interface was
"ribbonized" in Access 2007). Only caution is that, Real Soon Now, Microsoft
will be dropping all support, even security updates, for Access 2003. On the
other hand, I know a few people who are still using Access 97 as their
primary version, because they are convinced that it is the _best_ version
ever released, and Microsoft support has not been available for some years.
Thanks again for your response. I hope
all this makes sense but unfortunately I
am no further ahead at the moment.
Click to expand...

Well, I'm not the best authority on date manipulation. I've worked on only a
few "international" applications, and all of those were "US English only",
not multi-language.

And, because of the effect of regional settings, changing Operating Systems
could affect the way dates are formatted.
 
In addition to what Larry's told you, see what Allen Browne has at
http://www.allenbrowne.com/ser-36.html


"TeeSee" wrote in message

Access 2003, WIN 7, Regional settings= English, Canada

No matter what I try formatting date field in tables, formatting or
not during insert to tables using strDate = "#" & mydate & "#" Icannot
get the table updated to dd-mm-yyy. The following is a trial SQL print
out that looks correct and Insert to the table okay but in mm/dd/yyyy
format

INSERT INTO tblDates(dtmDate1,dtmDate2)VALUES(#04/06/2012#,
#11/06/2012#) .... The dates are the fourth and eleventh of June 2012.
When inserted to the tbl they show as 06/04/2012 etc.
This seems to have started when I changed from XP to Win 7
Could someone please advise how to correct.

Thank you
 
Back
Top