Comparing time fields to next time field??

[rorymo]

I have a report that shows these fields (among others):
Ref# CloseTime

When the reference numbers are the same, I need to compare
the CloseTime fields and print the one that is the earliest
time-wise.

Example:
205819 1/14/04 15:10
205819 1/14/04 15:36
205819 1/15/04 6:54

205835 1/14/04 14:57

I would need to print:
205819 1/14/04 15:10

I am using this formula:
if ({ref_num} = Next({ref_num}))
then
if ({act_log.time_stamp} >
Next({act_log.time_stamp}))
then
DateAdd ("s",({act_log.time_stamp}-21600),#1/01/1970#);

This correctly prints the
205819 1/14/04 15:10,
but also prints
205819 1/15/04 6:54.
I am guessing this happens because of the date change to
the fifteenth.
I keep getting the error message:
"Dates must be between year 1 and year 9999" when
I try to convert the number to a date for more granular comparison purposes.
If anyone has a way to do this, I'd appreciate the help.
Thanks,
rorymo

 

[hilfy]

If you group by Ref#, you can add a summary field that will show the "Minimum" value of the field.

-D

 

[rorymo]

Thank you!
I'll try it right now.
rorymo

 

[rorymo]

I grouped them by reference number, then did a "min" summary on the timestamp field, the numbers are off.
the example I should have gotten the number
074,114,647. It gave me 1,074,111,560.
I also don't know how to get it back to date format,
I use :
DateAdd ("s",({act_log.time_stamp}-21600),#1/01/1970#) on the other fields,
but don't know how to do that on the "min" result field.
All help will be greatly appreciated!
rorymo

 

[lbass]

As long as you group on {ref_number}, I think you could go to report
->edit selection formula->GROUP and enter:

{act_log.time_stamp} = minimum({act_log.time_stamp},{ref_number})

This will return only the record with the minimum time for each ref number. But use your formula dateadd formula in the body of the report--in other words, you don't have to do the conversion when doing the group select, you can just do that for display in the report.

-LB

 

[rorymo]

Thanks for your help!
I'll try it!
rorymo