Short date being ignored from UPDATE query in 2016 table

Question

I have a table that excepts some dates. I have the field formatted for short date. In VBA I have an UPDATE statement that sends a short date to the DB. When I open the table I see that the field not only has a time element it also has the default date of 12/30/1899 totally ignoring the value in the statement. Below is an example of the sql query being executed on the DB.

UPDATE Projects Set Start = 10/23/2015, End = 1/1/2016, DepartmentCreated = True, Completed = True WHERE projectID = 5

I cannot figure out what is happening and it is driving me nuts. As you can see that is clearly a short date and the DB simply ignores it. I get no errors and anything to indicate an issue.

Ty

Answer 1

Hi Ty. Values other than numbers need delimiters. For dates, it's the hash tag. For example:

UPDATE Projects SET Start=#10/23/2015#, End=#1/1/2016#, DepartmentCreated=True, Completed=True WHERE ProjectID=5

Hope that helps...

Answer 2

That worked. Thanks DB.

I don't remember ever having to do that in VB sql but no matter. It works.

Answer 3

Hi. You're welcome. Glad to hear you got it to work. Good luck with your project.

Answer 4

When I open the table I see that the field not only has a time element it also has the default date of 12/30/1899..............

That's because, without delimiters, a value such as 10/23/2015 is an arithmetical expression which evaluates to 2.1577300679685E-04, which is 0.00021577300679685 in decimal notation.  The date time data type in Access is implemented as 64 bit floating point number, with the days represented by the integer part, and the times of day by the fractional part.  Zero represents 30 December 1899 00:00:00.  So the value returned by the arithmetical expression is  0.00021577300679685 days from 30 December 1899 00:00:00, which is 30 December 1899 00:00:19.  You can easily see this in the debug window with:

? Format(10/23/2015, "dd mmmm yyyy hh:nn:ss")
30 December 1899 00:00:19

---

Ken Sheridan, Stafford, England