DateDiff calculation with criteria returns a negative number

[Windsorcat via AccessMonster.com]

My query calculates a date between a program completion and the date of a new
offense. Since some offenders have entered the program more than once, the
offense dates might be before the second or third attempt at program
completion, which then returns a negative result.

I am specifiying a completion date of say between 1/1/01 and 12/31/01, then
my DateDiff looks like this:
recid: DateDiff("d",[Completion],[OffenseDate]) and the criteria is <=365
(for 1 year recidivism)

I get result:
Completion OffenseDate recid
6/6/2001 1/16/00 -507
12/29/2001 3/11/01 -293

How can I specify not to look at offense dates that are before the completion
date?

Thanks for your help!

 

[John Vinson]

My query calculates a date between a program completion and the date of a new
offense. Since some offenders have entered the program more than once, the
offense dates might be before the second or third attempt at program
completion, which then returns a negative result.

I am specifiying a completion date of say between 1/1/01 and 12/31/01, then
my DateDiff looks like this:
recid: DateDiff("d",[Completion],[OffenseDate]) and the criteria is <=365
(for 1 year recidivism)

I get result:
Completion OffenseDate recid
6/6/2001 1/16/00 -507
12/29/2001 3/11/01 -293

How can I specify not to look at offense dates that are before the completion
date?

Use a criterion in the Query of

[Completion]

on the OffenseDate field.

John W. Vinson[MVP]