[Last Call] Learn how to a build a cloud-first strategyRegister Now


Default Null Dates to Zero Value Number

Posted on 2009-12-30
Medium Priority
Last Modified: 2012-05-08
I'm using MSQuery in Excel to query an Access table.  I HAVE to use MSQuery.  I'm running into a problem since MSQuery cannot handle null dates with something like an NZ function in Access.  So I think I'm going to have to alter the date fields in my Access table so MSQuery can query it.  Currently the Date/Time field in the Access table can hold nulls.  How do I set the null date/time values to the equivalent of 0 in the Access table?  Is there such a value?  I ask because I remember querying a table a while ago that when the results exported into Excel, the actual value was the date 1/0/1900 ----which equates to 0 in Excel.  I will be outputting the query results in Excel so I need something that won't equate to a "real" date that can confuse the query results.  Also, what is the best way to set the null date/times to a 0 equivalent date, through a NZ() update statement or set it at the property level of the table itself?  Thanks.
Question by:error_prone
LVL 28

Expert Comment

ID: 26147333
Might be easier to create a query in Access to query the table and use the NZ function to set the nulls to 0.  Then use MSQuery to query the query.  That's a lot of queries in one sentence.

OM Gang
LVL 77

Accepted Solution

peter57r earned 2000 total points
ID: 26147412
a datetime value of 0 would equate to 31/12/1899 and that is what both Access and Excel will display in a  field/cell formatted as date.  I think you would have to do explicit tests to prevent such a display in Excel.
LVL 11

Expert Comment

ID: 26147691
Well for course there is no zero date, and the numeric value 0 equates to 12/31/1899 as Peter said.  I think the simplest thing would be for you to arrange for nulls to be replaced with some arbitrary standard date value, say 1/1/2500, and then inform the users of the output that 1/1/2500 indicates no date is available.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

ID: 26148237
I'm running an append query to populate this Access table which has the null date/time fields.  I deleted the table and set the default value of the field to #12/31/1899# and re-appended the table.  But that didn't work, (not sure why).  I'm running an update statement right now on the field....

Author Comment

ID: 26148309
Ok, updating the field to 12/31/1899 worked.  

Author Closing Comment

ID: 31671344
I'm going to have to play around with Excel to get that value to conditionally format to white font, (ie hide the value).  It's not working the same as the value 1/0/1900 for some reason.  However, if I put the equal sign in front of 12/31/1899 it changes to 1/0/1900 in Excel.  Odd...

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

830 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