Want only one "previous inspection" for fire dept

[emscaptain]

I am really reaching trying to work in Crystal Reports so any help in simple terms (or a corrected formula) is appreciated. I have this formula in a report- if {Inspect.Inspection Date} > 0 then Date(1800,12,28)+{Inspect.Inspection Date}. I want it to show only one previous inspection, the most recent one. Is there a way I can limit it to one record?

 

[emscaptain]

Maybe I should add that in some cases there are no previous inspections in which case it will be left blank.

 

[kskid]

A little more information would be helpful

Crystal Version
Database and Connectivity
Sample data (and links)
Expected output

With what little information you provided, it a little difficult in determining the right solution for you

Assuming you have two tables linked by UnitID

Unit is a table of units to be inspected.
Inspections is a record of inspections

Inspections is left outer joined to units. This will identify those units that do not have any inspection records

Insert a group on {Table.UnitID}

Click on Select Expert->Show Formula and check Group formula and click on Formula editor and add the following

 isnull({Inspections.date}) or {inspections.date} = maximum({Inspections.date},GroupTable.Unitid)

The isnull will pick up those units that have no inspection and the maximum will pick up the latest inspection datre for that unit.

-lw

 

[lbass]

First sort your records by {table.date} in ascending order. Then try a formula like this:

if {table.date} = maximum({table.date},{table.groupfield}) then
if previousisnull({table.date}) then date(0,0,0) else
previous({table.date})

You could display this either in the detail section or in the group footer.

-LB