Formatting DateTime for SQL

One way to format a datetime for sql is:
Format({date expression}, "\#mm/dd/yyyy hh:nn:ss\#")

However I came across this page which shows a different and rather bizarre alternative.
http://www.mvps.org/access/datetime/date0005.htm

This string makes no sense to me at all:
"\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"

This definitely works but must include that weird bit on the end if the forward slashes and colons are (presumably) escaped with the backslashes like that. Unfortunately it is not explained at all on the page.

The ending looks a lot like an ofcuscated Null to me and what is with the three semicolons? Moreover why use this when the simple alternative works? Is it a version thing?

The simple string Googles 63K hits while the other produces just four, one being the mvps page I have already linked and three asking for help with problems. Unfortunately the mvps page has a very high page rank and no doubt confuses many people.

Unfortunately I have just given it more status at Google.

Hi

It's actually just a little out of date.
Formatting has changed since VBA version 5 (when the article was created).
In VBA prior to Office 2000, that would have worked as expected.

Behaviour now varies considerably depending upon the data type of the formating.
The semi colons provide sections to optionally format the result depending upon the value passed (for example negative or Null values).

In this case - the intent is that if a Null value is passed as the expression to the Format function - that the text "Null" is returned.
(As you correctly surmise - the slashes are simply escaping any possible alternative interpretation of the characters N,u and l.)

However in VBA 6 the date format doesn't support a fourth section (delimited by the semi colons). Had this been an integer format - then that would be fine. But with a Date you're getting nada, nichts, rein. (Well... a ZLS, but that's what you get from a String returning function ;-)

So, to summarise... the intent is that if you pass values to a function such as

1) Format(varDate, "\#mm/dd/yyyy hh:nn:ss\#")
2) Format(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l")
3) Format(varDate, "##.00;;;\N\u\l\l")

If varDate is Null then the return will be
1) ZLS ("")
2) ZLS ("")
3) "Null"

Had this been in VBA 5 (i.e. Access 97) then 2) would also have returned "Null" which was the intention of the article writer at the time.
The reason is simple enough.

Consider building a SQL string to insert a value to a table.
strSQL = "INSERT INTO tblDates (ID, DateField) VALUES (1, " & strDate & ")"

If you have formed strDate using a standard expression which returns the appropriately formatted date if a value is passed of the text "Null" if not then you get either
strSQL = "INSERT INTO tblDates (ID, DateField) VALUES (1, #12/12/2003 14:00:00#)"
or
strSQL = "INSERT INTO tblDates (ID, DateField) VALUES (1, Null)"

Either will execute without further concern.
Inserting a Null value into the field when Null was passed. Concatenating an actual Null into this leaves a poorly formed string which will fail.
strSQL = "INSERT INTO tblDates (ID, DateField) VALUES (1, )"

Alas we have to consider that seprately from the expression now anyway though.
A custom function would likely be best, rather than replacing the ZLS in situ with "Null".

Cheers.