However if you write code you need it in mm/dd/yyyy format.
Dates are stored internally as double precision numbers. That is how all relational databases work. The origin date is different depending on the RDBMS and the precision of the number but dates are not strings.
I can't say why MS (and others) decided on the US standard of mm/dd/yyyy as the default STRING date format. It certainly would have made more sense to use yyyy/mm/dd but no one asked me. Perhaps they settled on mm/dd/yyyy since doing so would only annoy half the world whereas using yyyy/mm/dd would annoy everyone. When working with dates in a region where the STRING default is NOT mm/dd/yyyy, you need to format the date to mm/dd/yyyy OR yyyy/mm/dd yourself but ONLY if you are creating an SQL String.
For example, if you are using a querydef and you are referencing a form control, as long as that form control is either bound to a date data type field or has its format property set as a date regardless of your local format then -
Select .... From ... Where SomeDate = Forms!yourform!yourdatecontrol
Will work.
However, if you build the same query in VBA, you are creating a STRING and the Access expression engine cannot help you so you need to specifically format the date.
strSQL = "Select ... From ... Where SomeDate = #" & Format(Me.yourdatecontrol, "mm/dd/yyyy") & "#"
So in the first example, the Access expression evaluator knows it needs to format the date field as the SQL engine expects it and in the second example, you are building the string so you are responsible for the formatting if necessary.
For your own sanity, there is nothing wrong with you always using yyyy/mm/dd for string formats.
strSQL = "Select ... From ... Where SomeDate = #" & Format(Me.yourdatecontrol, "yyyy/mm/dd") & "#"
Even dd/mm/yyyy format works as long as day is > 12 so it can't be confused for a month. This tends to confuse people because sometimes their date works but sometimes it doesn't. This is just Access being too helpful. It would probably be better if Access complained about an invalid date rather than trying to help us.
One thing to ALWAYS remember is to NEVER, EVER format a date EXCEPT for display. When creating queries that work with dates, no formatting is required UNLESS you are using a string date. So comparing one date field to another in a query, even one built with VBA works correctly as long as you do not format the date fields. The only time I ever format date fields in a query is if I want to export the query to Excel and I want to specifically remove the time component. Otherwise, formatting, if necessary, should be done using form control properties or by simply leaving the format alone and relying on your regional settings.
When I make databases that will be used on both sides of the pond, I do format dates but I use dd-mmm-yyyy which is non-ambiguous and people like it better than yyyy/mm/dd
