Null and Dates

[Sharon]

I use the following calculation to determine the amount of
months between the [last_review_date] and the
[next_review_date].

Cycle: DateDiff("m", [next_review_date],
[last_review_date])

Where does the Nz go in this calculation for records that
do not have both dates yet?

 

[Chuck Minarik]

Try this:

If ((nz([next_review_date], 0) <> 0) AND _
(nz([last_review_date], 0) <> 0)) Then

Cycle: DateDiff("m", [next_review_date], _
[last_review_date])
End If

Good Luck,

Chuck

 

[Guest]

Chuck,
Should this code go into a query or into VB?

-----Original Message-----
Try this:

If ((nz([next_review_date], 0) <> 0) AND _
(nz([last_review_date], 0) <> 0)) Then

Cycle: DateDiff("m", [next_review_date], _
[last_review_date])
End If

Good Luck,

Chuck

-----Original Message-----
I use the following calculation to determine the amount of
months between the [last_review_date] and the
[next_review_date].

Cycle: DateDiff("m", [next_review_date],
[last_review_date])

Where does the Nz go in this calculation for records that
do not have both dates yet?
.

.

 

[Chuck Minarik]

The way I originally answered would go in VB.

If you want to do it in a Query, add a column for each date
like so:

Expr1: nz([next_review_date], 0) and
Expr2: nz([last_review_date], 0)

In the Criteria row below each of these columns, add the
criteria:

<> 0

Then your original column for Cycle should compute just
fine.

Chuck

-----Original Message-----
Chuck,
Should this code go into a query or into VB?

-----Original Message-----
Try this:

If ((nz([next_review_date], 0) <> 0) AND _
(nz([last_review_date], 0) <> 0)) Then

Cycle: DateDiff("m", [next_review_date], _
[last_review_date])
End If

Good Luck,

Chuck

-----Original Message-----
I use the following calculation to determine the amount of
months between the [last_review_date] and the
[next_review_date].

Cycle: DateDiff("m", [next_review_date],
[last_review_date])

Where does the Nz go in this calculation for records that
do not have both dates yet?
.

.

.

 

[John Vinson]

I use the following calculation to determine the amount of
months between the [last_review_date] and the
[next_review_date].

Cycle: DateDiff("m", [next_review_date],
[last_review_date])

Where does the Nz go in this calculation for records that
do not have both dates yet?

What answer do you want if one of these dates is unknown? How many
months are there between #1/29/2004# and (Unspecified date)? I don't
think it makes any sense to do this calculation at all unless you know
both dates; can you just put a criterion of IS NOT NULL on the two
fields so that you only calculate a value if they are known?