Access 2010 Handling of SQL 2008 dates

Posted on 2011-05-09
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
    LVL 12

    Assisted Solution

    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

    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

    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

    - 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

    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

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now