Null and Dates

  • Thread starter Thread starter Sharon
  • Start date Start date
S

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?
 
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
 
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?
.
Click to expand...
.
Click to expand...
 
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?
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
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?
Click to expand...

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?
 
Back
Top