Limiting data returned in sub-report to 1st record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rose B - 23 Apr 2008 18:30 GMT

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!

Reply to this Message

Marshall Barton - 23 Apr 2008 20:37 GMT

>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?

Clear enough ;-)

A subreport with no records will never display anything so
it can not display "None".

OTOH, a simple way to get that effect is to place a main
report text box on top of the subreport control and set the
text box's expression to:
=IIf(subreportcontrol.Report.HasData, Null, "None")

Signature

Marsh
MVP [MS Access]

Reply to this Message

Rose B - 23 Apr 2008 22:08 GMT

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!

> >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
[quoted text clipped - 11 lines]
> text box's expression to:
> =IIf(subreportcontrol.Report.HasData, Null, "None")

Reply to this Message

Rose B - 23 Apr 2008 22:22 GMT

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

Reply to this Message

Marshall Barton - 23 Apr 2008 23:09 GMT

>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.

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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Rose B - 24 Apr 2008 07:45 GMT

Thanks for the advice Marshall- I will do that.

> >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
[quoted text clipped - 6 lines]
> you have a tie breaker field, then add it to the ORDER BY
> clause.

Reply to this Message