One way I can think of to do this is to add a new field in the query your
report is based on, and sort on that.
(Aircode)
SortField: iif(Right([Ticket],1) <> "*", [RecNum],
DLookup("[RecNum]","NameOfTable","[Ticket] = '" & Left([Ticket],
Len([Ticket])-1) & "'")
The idea being that, for each ticket, SortField will 1) if it is a non-*
ticket, contain its own RecNum or 2) if it is a * ticket, look up the RecNum
for that Ticket# without the *. That way both 88899 and 88899* will have
something *exactly* in common (which they don't now) that you can group on.
However, DLookup is a very brute-force way of doing this. Depending on the
amount of data & processing power involved, you might find it unacceptably
slow.
Alternatively, create a Totals query that GroupsBy BaseTicket# (stripping
off any *s), and calculates Min(RecNum). Use that as the RecordSource of
your parent report. Then create a subreport based on a different query that
shows the * and non-* detail, using BaseTicket/Ticket as the Master/Child
links.
HTH,
--
George Nicholson
Remove 'Junk' from return address.
babs said:
The # of Characters for the ticket # can vary - and I still want to only
sort
by RECORD# not Ticket # unless the last character is an *. So bottom line
is
I want all sorted by Order entered(record#) unless it looks the same in
the
front and then ends with an * - then I want it to go directly under the
ticket # it is like. 88899 and 88899*
Thanks so much,
Barb
Rick B said:
Why not just use...
=Left([ticket#],3)
as your primary sort
And
=Right([Ticket#],1)
as your secondary? The only time the secondary would be looked at is if
the
left three were the same.
Rick B
babs said:
I have my report(an invoice) sorted by the order the records were
entered(
record #). A few records will have a duplicate ticket# but followed by
an
asterick(*). Example
ticket, 888 and 888*. I would like to keep the sort for the report in
record# order except if the ticket # ends in a * and then I would like
it to
fall directly after the ticket # it is like. Can I use an if then in
the
group and sorting dialog box - not sure how to do it????
Please help,
Barb
