Limiting data returned in sub-report to 1st record

[Rose B]

I have a report with a sub-report and the sub-report has a select query that
may return 0 or n records. If 0 records are returned then I want to set a
field on the sub-report = "None", if there is 1 or more record then I want to
set it equal to the value of a field in the 1st record and ignore the rest.
Is there a way to limit the number of records returned in this way?

I hope that this is clear!

 

[Rose B]

Thanks fo rthe handling of no records - how about to limit the results to the
1st record? I have tried using SELECT FIRST in the SELECT statement, but for
some reason, whilst it limits the results to one the sort isn't working so it
is not showing the correct record. (Not sure if it will help but my select
statement, without adding "First" to the SELECT fields and the ORDER BY field
is
SELECT Trip.MileageOut, Trip.[Date of Trip]
FROM (qryJobsNotInvoiced INNER JOIN Trip ON (qryJobsNotInvoiced.ClientID =
Trip.ClientID) AND (qryJobsNotInvoiced.DestinationID = Trip.DestinationID))
INNER JOIN TripOffered ON Trip.TripID = TripOffered.TripID
ORDER BY Trip.[Date of Trip] DESC;


......any help greatly appreciated!

 

[Rose B]

Yay!!!! Just found that if I "SELECT DISTINCT TOP 1" it seems to work. Bit
more testing but I think I might have cracked it. Thanks so much for your
help.

Rose

 

[Rose B]

Thanks for the advice Marshall- I will do that.

That's about what I would have suggested.

There is a caveat to using TOP though, it will return all
the records that match the top value in the sort order. If
you have a tie breaker field, then add it to the ORDER BY
clause.