Lapsed Time when 1 date is null



I am trying to create an Aging Report and calculate the number of NetworkDays
between 2 dates in Access 2007. Often times, the 2nd date is Null. In those
cases, I want to calculate the Datediff between the first date and Now().
But if the 2nd date is NOT Null, then I want to do a regular

I like the NETWORKDAYS function in Excel in that it will remove the Holidays
you specify. How do I incorporate that into the Datediff function as well in
this case.

I'm still new at this. I know there must be some VBA or Macro or Expression
I can do in my query to get the desired result.

John Spencer

First question. Use the NZ function to force today's date if the field is null.


The equivalent of NewWorkDays is a VBA function. Or a calendar table that
flags non-work days.

Right now (for some reason) I can't find my references to posted VBA
functions. Hopefully someone else will post the URL's. If not, I will
eventually find my notes on the locations.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Daniel Pineault

1. Null issue
You'll have to test for a null value and then act accordingly. IE:
dt1 = Me.DateControl1
If IsNull(Me.DateControl2)=True Then
dt2 = Date
dt2 = Me.DateControl2
End If
or instead of IsNull you might use IsDate

Then proceed with your calc.

2. NetworkDays
For this look at the pre-made functions available at

Hope this helps,

Daniel Pineault
For Access Tips and Examples:
Please rate this post using the vote buttons if it was helpful.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question