By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

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

P: 49
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:

Expand|Select|Wrap|Line Numbers
  1.     strSQL = "UPDATE " & strWriteTable & " " _
  2.     & "SET AImportPropIndex = " & intPropIndex & ", " _
  3.     & "AImportDate = " & dtDate & ";"
  4.  
  5.     'DoCmd.SetWarnings False
  6.     DoCmd.RunSQL strSQL
  7.     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:

Expand|Select|Wrap|Line Numbers
  1.     strSQL = "CREATE TABLE " & strWriteTable & " " _
  2.     & "(AImportIndex INT, " _
  3.     & "AssessmentItemIndex INT NOT NULL, " _
  4.     & "AssessmentRShort INT, " _
  5.     & "AssessmentRFreeText TEXT(255), " _
  6.     & "AssessmentRComment TEXT(255), " _
  7.     & "AImportPropIndex INT, " _
  8.     & "AImportDate DATETIME, " _
  9.     & "PRIMARY KEY (AImportIndex));"
  10.  
  11.     DoCmd.RunSQL strSQL
Does anyone know why this problem would be occurring? And the best way to circumvent it? Thanks.
Mar 17 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
9 Replies


P: 49
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...
Mar 17 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Mar 17 '10 #3

P: 49
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.
Mar 17 '10 #4

NeoPa
Expert Mod 15k+
P: 31,186
@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).
Mar 17 '10 #5

P: 49
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?
Mar 18 '10 #6

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. 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) :
Expand|Select|Wrap|Line Numbers
  1. SELECT 05/03/2010 AS [CurrentDate],
The USA version would be equally incorrect as :
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT Date() AS [CurrentDate],"
Which would resolve to :
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT #" & Format(Date(), "m/d/yyyy") & "# AS [CurrentDate],"
This resolves to (correct no matter where you are in the world) :
Expand|Select|Wrap|Line Numbers
  1. SELECT #3/5/2010# AS [CurrentDate],
Mar 18 '10 #7

P: 49
Sweet! Will come back to this thread as the need arises.
Mar 18 '10 #8

NeoPa
Expert Mod 15k+
P: 31,186
@JeremyI
I interpret that as All fully understood. Good news :)
Mar 18 '10 #9

P: 49
Absolutely. :-) Thanks again.
Mar 22 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.