By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,377 Members | 1,706 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,377 IT Pros & Developers. It's quick & easy.

Date problem: suddenly transposing day and month a la USA on one form

P: 99
Something very mad is happening. On a form there is a text box into which a date (in format dd/mm/yy) is entered (the default being today's date). When the record is saved, it goes into the table as mm/dd/yy. So, for example, if the date entered is 10/04/10 (10th April 2010) it goes in as 04/10/10 (4th October), and comes out as that whenever it's retrieved thereafter.

There are lots of places in the db where dates are entered and saved, and this problem occurs nowhere except on this one form. There's no code behind the date field, and nothing odd about the table: format for both is just set to Short Date.

I have checked the Windows Regional Settings, and date/time is set to UK ... anyway, that setting should apply everywhere (if US then US everywhere, if UK then UK everywhere).

I've been ferreting about for the source of this problem for HOURS!!! Help! Has anyone else experienced this 'madness'? If so, what can be done to stop it?
Apr 10 '10 #1

✓ answered by missinglinq

Allen Browne, who lives in a British Commonwealth nation (do they still call it that?) has an excellent treatise on the subject, that may help:

http://www.allenbrowne.com/ser-36.html

Linq ;0)>

Share this Question
Share on Google+
10 Replies


missinglinq
Expert 2.5K+
P: 3,532
Allen Browne, who lives in a British Commonwealth nation (do they still call it that?) has an excellent treatise on the subject, that may help:

http://www.allenbrowne.com/ser-36.html

Linq ;0)>
Apr 10 '10 #2

P: 99
Brilliant article, which I have book-marked. I now know exactly what the problem is (i.e. it's because I'm using a temporary table filled in code) but I haven't solved it, because his function SQLDate returns a string, which is the wrong data type for the table into which it is being saved. Can't change that field in the table into a string because then it won't sort properly by date.

Anyway, now I know where the problem is I can start to figure out a different solution, avoiding the temporary table.
Apr 10 '10 #3

missinglinq
Expert 2.5K+
P: 3,532
Glad it helped! Allen is known in some quarters as the "Wonder from Down Under!" A number of his papers have actually been co-opted by Microsoft's Knowledge Base for Access.

Good luck in finding a workaround!

Linq ;0)>
Apr 10 '10 #4

P: 99
Oh dear! Several hours later I've failed to work around the problem, as the alternative query is insanely complicated and I can't get it to work. So, I reverted to the original code form.

The code includes the following SQL and then runs it

Expand|Select|Wrap|Line Numbers
  1.          sqlStr = "INSERT INTO TEMP_FOR_ALL_GOOD_DEEDS " _
  2.                     & "(DeedID, EventID,ContactID, " _
  3.                     & "DeedType, EventTitle, StItemTitle, MoneyValue, GiftAid, " _
  4.                     & "CorporateOrPersonal, DateOfDeed,Notes) " _
  5.                 & "VALUES (" & DiD & "," & EvID & ", " & ContID & ",'" & DType _
  6.                 & "','" & EvTitle & "','" & StItemTitle & "'," & moneyVal & "," & _
  7.                      rstDeeds!GiftAid & ", '" & rstDeeds!CorporateOrPersonal & _
  8.                      "',#" & rstDeeds!DateOfDeed & "#, '" & rstDeeds!Notes & "') "
  9.          DoCmd.RunSql
  10.  
It works (or did before I messed about here trying to get shorter lines for you) except that, as per my original Q,
#" & rstDeeds!DateOfDeed & "#
gets stored as mm/dd/yy instead of dd/mm/yy. Thanks to the article you pointed me at, I now know why this is happening, but I still don't know how to cure it. If I make the date into a string using Alan's function, and change the field in the temporary table to a string (Text) I can get the date in there 'the right way round' as a string, but that's no good because I want to order the table by these dates.

Is there any way of telling the INSERT statement how to format the date? I'm pretty stuck!!!
Apr 10 '10 #5

P: 99
The solution is only slightly less bizarre than the problem!

If instead of running an sql statement to get the records into the temporary table you do it using a recordset for the temp table and putting the records in one by one using 'rst.AddItem' the problem doesn't happen. Dates go in just fine.

I hope that may help someone else out, as I feel a bit selfishly 'me,me,me' about my use of this site - nice to contribute something useful at last!
Apr 10 '10 #6

NeoPa
Expert Mod 15k+
P: 31,768
It may be related to the fact that literal dates in SQL are formatted as m/d/y entirely regardless of the regional settings. This would not have any effect on Recordset processing within your code, but if you created a SQL string in your code that set up date literals (using # chars) and didn't take this into consideration, then you could certainly expect this problem. Being as you're from Britain, you're more likely to fall foul of this than someone from the States. Many of our American experts (and some without even that excuse) still overlook this issue when giving advice about date usage. Check out Literal DateTimes and Their Delimiters (#) for more on this subject.

PS. posted code on the internet often suffers from this issue because it works whenever tested in the USA. This is certainly true of the code you posted in your post #5. It will not work correctly in Britain.
Apr 13 '10 #7

P: 99
Yes, that's just how it is with literal dates! It's annoying, because it seems much neater on many occasions to 'run' some SQL direct rather thand opening a recordset and being all 'procedural' about it.
Apr 14 '10 #8

NeoPa
Expert Mod 15k+
P: 31,768
It's still perfectly possible to do. All you need to do is to format (use Format() for this) the date in SQL date format (m/d/yyyy). It's explained with examples in the linked article.
Apr 14 '10 #9

P: 99
Thank you. sorry about the delay in saying 'thanks' ... I've been away looking after daughter's puppies!!!
Apr 24 '10 #10

NeoPa
Expert Mod 15k+
P: 31,768
You're most welcome :)

We don't panic too much about delays on here. There are always going to be issues interfering with access. We're just happy you got your issue resolved and understood.
Apr 26 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.