Dates and Null

[Guest]

I need to determine the # of months,[cycle], between the
[last_review_date] and the [next_review_date]. Some of
the records have empty review date fields.

My code listed below is not working.

Please advise to code content and structure for this
novice...

If ((Nz([Next_review_date], 0) <> 0) And (Nz
([last_review_date], 0) <> 0)) Then
[Cycle] = [Next_review_date] - [last_review_date]

 

[Anne Nolan]

Check out the DateDiff function... use that instead of your date
subtraction.

Anne

 

[Graham R Seach]

This should do it for you.

Public Function HowManyMonths(vStart As Variant, vEnd As Variant) As Integer
HowManyMonths = DateDiff("m", Nz(vStart, Date), Nz(vEnd, Date)) -
(Nz(vStart, Date) > Nz(vEnd, Date))
End Function

Call it thus (in a query):
Months: HowManyMonths([last_review_date], [Next_review_date])

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia