The eternal date question

Results 1 to 2 of 2

Thread: The eternal date question

  1. #1
    Join Date
    Dec 1969

    Default The eternal date question

    Hi, <BR><BR>I&#039;ve got an Access db which I use to run a forum on. I&#039;m having a lot of trouble with dates. <BR><BR>When a new message is logged, amongst other things I insert the date the message was logged, e.g.<BR><BR>INSERT into forum_table (fldDate) VALUES (#" & date & "#)<BR><BR>I&#039;ve got no special formatting on the way the date is defined in Access - it just gets entered as is. <BR><BR>A call is logged for example on October 29th 2001. The date entered into Access is 29/11/2001. That&#039;s fine, just as I want it - dd/mm/yyyy. <BR><BR>The trouble comes when the number of days in the date is less than 13 - it gets my days confused with months. So if a call is logged today the date entered into Access will be 11/08/2001. The reason this is causing such a big hassle is that I want to flag up any new messages in the forum - i.e. the most recent date. I can&#039;t do that as it obviously thinks August is further back than November, so any news calls logged with a day value less than 13 screw things up. Is there anything I can do to sort this problem out? <BR><BR>Maybe I&#039;ll have to sort out the newest messages based on messageID rather than date? <BR><BR>Thanks<BR><BR>Jim

  2. #2
    Join Date
    Dec 1969

    Default The solution to the eternal date question

    You get this problem because Access expects all dates to be in US format. So 08/11/01 is seen as 11th August 2001.<BR><BR>Just to confuse us, MS decided that if the date wasn&#039;t a possible US date (month greater than 12), it would try and use it as a UK style date.<BR><BR>There are a couple of ways around this:<BR><BR>1) Switch your Locale to the US before running the Insert query, and then back to UK after it has done the update:<BR><BR>Session.LCID = 1033 &#039; US dates<BR>&#039;Do the insert<BR>Session.LCID = 2057 &#039; UK Dates<BR><BR>2) Send the query a date that is not ambiguous:<BR><BR>INSERT into forum_table (fldDate) VALUES (#" & FormatDateTime(date(),1) & "#)<BR><BR>This will then send the following to Access:<BR><BR>INSERT into forum_table (fldDate) VALUES (#08 November 2001#)<BR><BR>As you can see, there can be no confusion, and your date will be stored correctly.<BR><BR>I&#039;m not sure what, if any, difference there is in performance, but I prefer the second way.<BR><BR>Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts