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

[julietbrown]

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?

[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)>

[julietbrown]

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.

[missinglinq]

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)>

[julietbrown]

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

         sqlStr = "INSERT INTO TEMP_FOR_ALL_GOOD_DEEDS " _
                    & "(DeedID, EventID,ContactID, " _
                    & "DeedType, EventTitle, StItemTitle, MoneyValue, GiftAid, " _
                    & "CorporateOrPersonal, DateOfDeed,Notes) " _
                & "VALUES (" & DiD & "," & EvID & ", " & ContID & ",'" & DType _
                & "','" & EvTitle & "','" & StItemTitle & "'," & moneyVal & "," & _
                     rstDeeds!GiftAid & ", '" & rstDeeds!CorporateOrPersonal & _
                     "',#" & rstDeeds!DateOfDeed & "#, '" & rstDeeds!Notes & "') "
         DoCmd.RunSql
 

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!!!

[julietbrown]

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!

[NeoPa]

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.

[julietbrown]

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.

[NeoPa]

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.

[julietbrown]

Thank you. sorry about the delay in saying 'thanks' ... I've been away looking after daughter's puppies!!!

[NeoPa]

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.