Access 2010 Handling of SQL 2008 dates

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.
Who is Participating?
OP_ZaharinConnect With a Mentor Commented:
- 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.
danishaniConnect With a Mentor Commented:
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:

OP_ZaharinConnect With a Mentor Commented:
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
bmusanteAuthor Commented:
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.
bmusanteAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.