Literal DateTimes and Their Delimiters (#).

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

Date Variables
Date variables do not need to be formatted when passed to 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.