Literal DateTimes and Their Delimiters (#).

[NeoPa]

The ANSI standards for SQL specify that literal dates should specifically be entered in m/d/y format. In this case (y) can refer to (yy) or (yyyy). This is completely independent of regional settings.

Delimiters
The delimiter character for dates, times or datetime combinations, is the hash (#). See (Quotes (') and Double-Quotes (") - Where and When to use them.) for more on literal delimiters

Formats
The Format() function in VBA can be used with these settings to provide the literal datetime required (NB I'm not aware of any lack of flexibility on the Time Format side so I merely include it to provide usable examples) :

Data Type (Example)             Format String     (Required Result)
Date      (1 February 2003)     '\#m\/d\/yyyy\#'  (#2/1/2003#)
Time      (6:15:42 PM)          '\#hh:n:s\#'      (#18:15:42#)
Date + Time                     '\#m\/d\/yyyy hh:n:s\#'
          (1 February 2003 6:15:42 PM)            (#2/1/2003 18:15:42#)

Common Problems
Like most interpreters of code in their various forms (VBA; SQL; etc), the MS Access Jet SQL interpreter can be clever (MS Access is notoriously forgiving) so will interpret your date literal correctly in many cases even when it is passed in the wrong format. 24 January 2005, for instance, would be worked out correctly in any format I have ever seen used.
Consider the date above though, 1 February 2003. It could be interpreted as 2 January 2003 if passed in British (d/m/yyyy) format or even as 3 February 2001 if passed in yy/m/d format. yyyy/m/d should always be safe though, if not quite standard.
Stick with the standard (m/d/y) if you require your code to be (more) portable though.

Gotcha
Related to the previous paragraph.
One thing it is very important to understand is that dates that are specified using a format other than the SQL standard (EG. A date of 13th February 2015 specified as #13/2/2015#.) will still be interpreted as a valid date if they can nevertheless be recognised. This can be a very hard situation to discover when testing and debugging code so be careful. It generally means the format used is incorrect but you weren't lucky enough to discover it during testing because it was between 13th and the end of the month.

NB.
Except in countries where the SQL format matches the local format, this will mean that a string will be interpreted one way when used in VBA, yet in another way when interpreted by SQL.

Regional Settings
Another, rather nasty, problem raises its head when dealing with Regional Settings in regions where the standard date format character (Slash (/) for many countries including USA & UK as well as various others in Europe) is replaced by any other character. Countries such as Denmark for instance, where their dates are in the format (d-m-y). This issue is really a problem with VBA and the Format() command specifically. A standard format string of "m/d/yyyy", when used by Format() is parsed and interpreted in light of the Regional Settings such that it is treated as if it were "m-d-yyyy" instead. This is clearly not good news when trying to produce a conformant string for a SQL command string. The way to get around this, and be fully portable internationally, is to use a backslash (\) before each slash (/) in the format string - as illustrated in the Formats section above. This ensures that the backslashes are not recognised as format characters, yet still end up in the same place in the returned string value.

Date Variables
Date variables do not need to be formatted when passed to SQL as a reference (as opposed to a literal string of characters within the SQL).
DateTime fields in a record, dates entered into parameters in a parameter query or any function returning a DateTime result will be correctly interpreted by the SQL engine IF IT IS INTERPRETED BY SQL and not pre-processed by Access into a date literal. In the latter case the format should be done in the standard fashion.

Debugging
It's easy to get some of this stuff wrong so I always recommend doing a 'Debug.Print strSQL' before passing the string to the SQL engine when developing the code, or even where you know there is a problem with it somewhere. Use Ctrl-G from the VBA window to show and go to the Immediate Pane where the string is displayed.

Debug.Print strSQL
DoCmd.RunSQL strSQL

More
Allen Browne has some further help on this topic (International Dates in Access).

[NeoPa]

It's come to my attention recently that another ANSI SQL standard for dates exists which is largely at odds with this other one explained here. MSDN has a lot more on it (Using Date and Time Data), but much of it is not compatible with Jet SQL (used by Access).

The other, reliable and standard form of a date literal that should work anywhere is "y-m-d" (Be careful here with Jet as yy may be treated as a day value. Only yyyy is 100% reliable in all circumstances). Again, this can be mixed with a time (except not in the ANSI standard way in Jet) to create a Date/Time value. I'm pretty sure it's also language and location independent. Access/Jet will always require hashes around the literal values though. No matter which format is attempted.

Some examples :

Formats
The Format() function in VBA can be used with these settings to provide the literal datetime required (NB I'm not aware of any lack of flexibility on the Time Format side so I merely include it to provide usable examples) :

Data Type    (Example)             Format String       (Required Result)
Date         (1 February 2003)     '\#yyyy\-m\-d#'     (#2003-2-1#)
Time         (6:08:42 PM)          '\#HH:nn:ss\#'      (#18:08:42#)
Date + Time                        '\#yyyy\-m\-d HH:nn:ss\#'
             (1 February 2003 6:08:42 PM)              (#2003-2-1 18:08:42#)

PS. I amended the format strings where the character '-' is used to avoid the risk of these being misinterpreted in countries, like Denmark, where this is the recognised date separator. It's possible it's not necessary for full portability, but I expect it is.