Access 2010 Handling of SQL 2008 dates

Posted on 2011-05-09
Medium Priority
Last Modified: 2012-06-22
Does access 2010/sql 2008 handle dates differently than Access 2002/sql 2000.  It appears that unless you default the dates to null in the database a check for nulls does not result in nulls.  This did work in 2002/2000 but does not work now.  I am converting my application to the new version and I have lots of code which checks for null dates.

Any ideas.
Question by:bmusante
  • 2
  • 2
LVL 12

Assisted Solution

danishani earned 800 total points
ID: 35725734
Its more likely a 'bug' or some different behaviour in Access 2010.

Do you use Unbound controls or Bound Controls?

See simular thread:

and also this thread:

LVL 23

Assisted Solution

OP_Zaharin earned 1200 total points
ID: 35734985
just sharing ideas:
- if thats the case, why not setting the Default Value = NULL for all the Date column so you don't have to change your codes and this will effect to all new data.
- after setting the Default Value to NULL, check also for existing data if your code check for null works. if not working, you might also have to update all existing empty/blank date column to NULL

Author Comment

ID: 35740550
Thanks for all of the great ideas.  I had already done what you have suggested prior to submitting the question and it works - I just am curious as to why Access 2010 has changed in the way that it handles these dates.  It appears in Access 2002 if you have a date control that has a default value of 0, the system will return a true condition if you test for nulls.  In Access 2010, you have to explicitly set the control with a default value of nulls for the system to test true for nulls.  My controls are unbound controls

In sql 2008 I am running into a similar problem with testing dates in stored procedures.  However this is a bit more mystifying because I do have my 2002 Access app running against the 2008 sql database and it works fine.  But when I run the 2010 Access app against the 2008 sql database I am not getting the same results.  This part of the problem will need more research.
LVL 23

Accepted Solution

OP_Zaharin earned 1200 total points
ID: 35742794
- what i've discovered is that, in Access 2007 they have put a system check for date only entry on date datatype column (i don't have 2003 to test this out). anything other than date will results a system error message will be prompt and that input will not be accepted. valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). date value of 0 represents December 30, 1899, therefore if you were saying you defaulted 0, it will be save as December 30, 1899 12:00AM, not null anymore.

Author Comment

ID: 35743046
It is true that a 0 would be saved as December 30, 1899 but in Access 2002 it would always test true as nulls.  This changed at some point - maybe in 2007 but it definitely does not work the same in 2010.

Again, I have fixed the problem - I just wondered if I was the only one who has experienced this.

I will divide the points to everyone who responded to me.  Thank you for your insight.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question