CRYSTAL 8 - SUBTRACTING YEARS OFF OF A DATE 3

[BRL123]

I am using Crystal 8 with an Oracle ODBC. I need to search for meters in need of an inspection based on a numeric interval of how often(in years)an inspection is required. My thought was to use a formula that pulls the meters with inspection dates that are < the currentdate - the interval but because the interval is just a number(ex: 1, 5,10 or 99) I am not sure how to subtract it from a datetime field.
Thanks,

 

[Madawc]

In 8.5, I used DateDiff("yyyy", {the.date}, CurrentDate). This should return the number of years - best to get it displaying before you try selecting with it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[synapsevampire]

I think that you want something like:

{table.inspectdate} < currentdate
and
remainder(year(currentdate),{table.year)) = 0

You should post example data and expected output.

-k

 

[lbass]

Wouldn't you want to use the last inspection date as your baseline date? You could create a formula like this:

//{@inspectiondue}:
dateadd("yyyy",{table.interval},maximum({table.inspectiondate},{table.customer}))

...assuming you have a group on {table.customer}. You could then use a group selection formula (report->selection formula->GROUP) like the following to determine which ones are overdue:

currentdate - dateadd("yyyy",{table.interval},maximum({table.inspectiondate},{table.customer})) > 0

Note that you have to write the formula out in the group selection, instead of using the formula name for this to work.

-LB

 

[synapsevampire]

LB: I think that the legal requirement is to inspect evry X years, so if they missed a date for some reason, it wouldn't be 5 years from the last inspection, it's still every 5 years from placement.

-k

 

[BRL123]

I appreciate all the help! I ended up adding the interval(number of years before an inspection is due) to the last inspection date if the newly created date falls within the user's date range then the report will tally the number of meters that are due to be inspected.

IF ( DATEADD("YYYY",{Meter.PERIODICINTERVAL},{Service.INSPECTEDDATE})) IN ({?BeginningDate} TO {?EndingDate}) THEN
1
ELSE
0