Lapsed Time when 1 date is null

[KerryC]

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
Datediff("d",date1,date2)

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.

DateDiff("d",[Date1],NZ([Date2],Date())

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
Else
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
http://www.mvps.org/access/datetime/index.html
Specifically
http://www.mvps.org/access/datetime/date0006.htm
http://www.mvps.org/access/datetime/date0012.htm

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.