Using MAXIMUM function

[piovesan]

Hi;
I am using Crystal Reports XI and I have a basic report that lists land purchased within a selected date range. One of the fields I want to display is a location (address). But there may be more than one address on the parcel. I need only to display one of those addresses. The address table is(called LND_CIVIC_ADDRESS) and besides the civic number field (Civ_Num), it also contains a unique ID called CIV_ID. But I fiqured where there was more than one address per land parcel, I would just use the Maximum Civ_Num. So I have created a formula called @maxcivic that looks like this:
if not isnull({LND_CIVIC_ADDRESS.CIV_ID}) then CStr (maximum({LND_CIVIC_ADDRESS.CIV_NUM}),0 ,"" )& ProperCase ( " " &{LND_CIVIC_ADDRESS.STR_NAME} & " " & {LND_CIVIC_ADDRESS.STR_TYPE} & " " & {LND_CIVIC_ADDRESS.GSA_NAME})

However, what this seems to do, is it finds the maximum civic number in ALL the records, and just uses that for all streets..... for example, if it is supposed to be:
12 Apple St
48 Pear St
200 Orange St

with the way I have it set up, it turns out like this:
200 Apple St
200 Pear St
200 Orange St

It does not seem to matter if I put this formula in the details section, or if I create a group section........it still turns out this way.......What do I need to change??
Thanks!

 

[lbass]

You need a group on the parcel field, and then add the group condition into your formula:

if not isnull({LND_CIVIC_ADDRESS.CIV_ID}) then
CStr (maximum({LND_CIVIC_ADDRESS.CIV_NUM},{table.parcel}),0 ,"" )&
ProperCase (" " &{LND_CIVIC_ADDRESS.STR_NAME} & " " & {LND_CIVIC_ADDRESS.STR_TYPE} & " " & {LND_CIVIC_ADDRESS.GSA_NAME})

-LB