Help with Date Difference Expression

[Rene]

Good morning,

Based on the following:

Lost Days = DateDiff("d",Nz([MyLostDaysBeginDate]),Nz([MyLostDaysEndDate]))

Restricted Days =
DateDiff("d",Nz([MyRestrictedDaysBeginDate]),Nz([MyRestrictedDaysEndDate]))

I need an expression that will subtract the Lost Days from the Restricted
Days, when the Lost Days date range is the same (or between) as the
Restricted Days date range. This will yield total Restricted Days.

Example:

Restricted Days were incurred from 02/01/09 to 03/01/09 and Lost Days were
incurred from 01/01/09 to 02/15/09. The overlap occurs from 02/01/09 to
02/15/09.

How can arrive at the Total Restricted Days?

Thanks in advance for your help,

Rene

 

[Arvin Meyer [MVP]]

In your query, create a new column:

TotalDays: [Restricted Days]-[Lost Days]

The square brackets tell Access that the alias column is to be used as a
virtual field.

 

[J Valentin]

I believe I understand Rene to be saying that the subtraction should only
occur for dates overlapped. I created a function that appears to cover this,
although it may need some more testing:

Public Function datec1(rdStart As Date, rdEnd As Date, ldStart As Date,
ldEnd As Date) As Integer

Dim restd As Integer, drdStart As Double, drdEnd As Double, dldStart As
Double, dldEnd As Double

drdStart = CDbl(rdStart)
drdEnd = CDbl(rdEnd)
dldStart = CDbl(ldStart)
dldEnd = CDbl(ldEnd)

If dldStart > drdStart And dldEnd < drdEnd Then
restd = DateDiff("d", drdStart, drdEnd) - DateDiff("d", dldStart,
dldEnd)
ElseIf dldStart > drdStart And dldEnd > drdEnd Then
restd = DateDiff("d", drdStart, drdEnd) - DateDiff("d", dldStart,
drdEnd)
ElseIf dldStart < drdStart And dldEnd < drdEnd Then
restd = DateDiff("d", drdStart, drdEnd) - DateDiff("d", drdStart,
dldEnd)

End If
datec1 = restd
End Function

Hope that helps.
J
http://www.spvision.com

In your query, create a new column:

TotalDays: [Restricted Days]-[Lost Days]

The square brackets tell Access that the alias column is to be used as a
virtual field.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Good morning,

Based on the following:

Lost Days =
DateDiff("d",Nz([MyLostDaysBeginDate]),Nz([MyLostDaysEndDate]))

Restricted Days =
DateDiff("d",Nz([MyRestrictedDaysBeginDate]),Nz([MyRestrictedDaysEndDate]))

I need an expression that will subtract the Lost Days from the Restricted
Days, when the Lost Days date range is the same (or between) as the
Restricted Days date range. This will yield total Restricted Days.

Example:

Restricted Days were incurred from 02/01/09 to 03/01/09 and Lost Days were
incurred from 01/01/09 to 02/15/09. The overlap occurs from 02/01/09 to
02/15/09.

How can arrive at the Total Restricted Days?

Thanks in advance for your help,

Rene

.