Access 2003: Frustrating date/time formatting switch in VBA update query

[JeremyI]

I am writing code to import assessment information about a building from an Excel file (populated by a mobile interviewer on a laptop) into an Access database. With much trial and error, I've hit upon a way of importing all the necessary information, but have run into one last problem when establishing my fully-prepared temporary table to use for appending to the permanent assessment data table.

One of the fields in the form, AImportDate, is a date/time field without a specified format. I have obtained the value of the assessment date and stored it as a variable dtDate, Dim'd as Date. For my test file, dtDate properly returns '05/03/2010' (5 March). I then run the following SQL command:

    strSQL = "UPDATE " & strWriteTable & " " _
    & "SET AImportPropIndex = " & intPropIndex & ", " _
    & "AImportDate = " & dtDate & ";"
 
    'DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

Bizarrely, this causes AImportDate for all of the records in the table to be updated with a value of '00:01:12', not a date at all, and certainly not the right one (if I change the format to Short Date in the table design, they show as '30/12/1899'). Yet if I manually type the date into the table, even after the update in the code, it displays correctly.

I had created the table with this SQL statement earlier in the module:

    strSQL = "CREATE TABLE " & strWriteTable & " " _
    & "(AImportIndex INT, " _
    & "AssessmentItemIndex INT NOT NULL, " _
    & "AssessmentRShort INT, " _
    & "AssessmentRFreeText TEXT(255), " _
    & "AssessmentRComment TEXT(255), " _
    & "AImportPropIndex INT, " _
    & "AImportDate DATETIME, " _
    & "PRIMARY KEY (AImportIndex));"
 
    DoCmd.RunSQL strSQL

Does anyone know why this problem would be occurring? And the best way to circumvent it? Thanks.

[JeremyI]

Right, tinkered a bit more and resolved the fundamental issue: I changed the variable type to Double (dblDate, heh heh). Now everything is fine.

Still happy to give a best answer to anyone who can explain why this happens...

[TheSmileyCoder]

While the variable dtDate is a date, your parsing it into a sql string. That converts the date into a string. To preserve the date literal, you need to enclose it in # on both sides. Try looking at this article:

Literal datetimes and their delimiters

I also believe I can tell you why using a double works. This bit is dragged up from memory so if not 100% accurate don't shoot me :)
Dates are stored as doubles, with the part before the decimal , seperator (or . seperator depending on where your from) being number of dates after a specific date (which i think is 31 of december 1899 or something) the part after the , being the time fraction.

[JeremyI]

I see! Yes, I recall seeing the # signs elsewhere but previously didn't know the proper use of them.

Exactly as predicted in your link, Access has muddled up the British-format date I passed and changed it to 3 May 2010. I think I'll stick with the Double format for this application.

Thanks very much.

[NeoPa]

@JeremyI
This is a misunderstanding of what's going on here.

Jet SQL (not Access as such) has correctly interpreted your muddled up date as 3rd May 2010. Just as it correctly interpreted your 5 ÷ 3 ÷ 2010 as a fraction which, when displayed as a fraction of a day, came out as 1 minute and 12 seconds.

It's all about presenting the data in the correct format. If you tell it to treat it as a number sequence it will. If you pass the 5 as the first portion of a date literal, it will treat it as May. That is the SQL standard. It is behaving perfectly predicatably (as we would all hope).

[JeremyI]

OK... I thought I was starting to understand, but now I'm re-confused, I'm afraid. If the cell in Excel from which the date value is obtained is formatted as dd/mm/yyyy (OS-dependent), what is happening with the date variable?

At what point are the delimiters supposed to be used?

[NeoPa]

That's a good question. It's covered by the paragraph titled Date Variables in the article, but it is nevertheless very difficult to get. Especially for those new to the whole concept.

The important idea is whether or not the SQL itself contains characters that point to where the date value can be found (variables of some form), or whether it contains ASCII characters representing the value itself. I'll include an example to illustrate. One that many get confused over :

strSQL = "SELECT " & Date() & " AS [CurrentDate],"

This is quite wrong!

The reference to the function Date() would normally be covered by the reference rule, which would mean it doesn't need the "#" characters. It doesn't appear to be a literal after all. In this case though, the reference is only within the context of the VBA. What is eventually processed as SQL is the strSQL string, which will simply contain (for 5th March 2010) :

SELECT 05/03/2010 AS [CurrentDate],

The USA version would be equally incorrect as :

SELECT 03/05/2010 AS [CurrentDate],

With no delimiters in either, they are both treated as number equations (5 ÷ 3 ÷ 2010 & 3 ÷ 5 ÷ 2010).

As Date() is a function also available to the SQL interpreter this could have been written with a valid reference as :

strSQL = "SELECT Date() AS [CurrentDate],"

Which would resolve to :

SELECT Date() AS [CurrentDate],

What you're really looking for here though is a literal date value. While Date() may be ok to reference from within the SQL, many other worked out (calculated) date values may not be, hence the requirement for properly formatted date literals :

strSQL = "SELECT #" & Format(Date(), "m/d/yyyy") & "# AS [CurrentDate],"

This resolves to (correct no matter where you are in the world) :

SELECT #3/5/2010# AS [CurrentDate],

[JeremyI]

Sweet! Will come back to this thread as the need arises.

[NeoPa]

@JeremyI
I interpret that as All fully understood. Good news :)

[JeremyI]

Absolutely. :-) Thanks again.