Link to home
Start Free TrialLog in
Avatar of error_prone
error_prone

asked on

Default Null Dates to Zero Value Number

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.
Avatar of omgang
omgang
Flag of United States of America image

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
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of error_prone
error_prone

ASKER

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....
Ok, updating the field to 12/31/1899 worked.  
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...