Sort a report by the value in a control

[Guest]

I need to sort a report on the value in a control field, but I can't figure
out how to do that.

My table source for the report has only FieldA-ID (Parent) and FieldB-ID
(Child). A separate table has the description for FieldA-ID and FieldB-ID.

My report of the table is set up to display just the description in the
Parent header:
=DLookUp("[MyDesc]","MyDescTable","[MyDescID] = " & Report!FieldA-ID)

and just the description in the Child detail:
=DLookUp("[MyDesc]","MyDescTable","[MyDescID] = " & Report!FieldB-ID)

My only 'sorting and grouping' choices seem to be 'FieldA-ID' and
FieldB-ID', so I get

Parent: Foo [MyDescID=01]
Child: Wally [MyDescID=72]
Dibert [MyDescID=90]

Parent: Bar [MyDescID=02]
Child: Dogbert [MyDescID=16]
Ratbert [MyDescID=25]

But what I want the report to look like is:

Parent: Bar
Child: Dogbert
Ratbert

Parent: Foo
Child: Dilbert
Wally

How do I do this?
Thanks in advance,
- Cathy

 

[Guest]

You can not sort by a "value."

You need a query with the tables joined. But your description of the table
I do not think you can.

Anyway, the query would pull fields Parent and Child. Use the query as
record source for the report. Set the grouping and sorting. Set the
property for the Parent to Hide Duplicates - Yes.

 

[John Vinson]

I need to sort a report on the value in a control field, but I can't figure
out how to do that.

My table source for the report has only FieldA-ID (Parent) and FieldB-ID
(Child). A separate table has the description for FieldA-ID and FieldB-ID.

My report of the table is set up to display just the description in the
Parent header:
=DLookUp("[MyDesc]","MyDescTable","[MyDescID] = " & Report!FieldA-ID)

and just the description in the Child detail:
=DLookUp("[MyDesc]","MyDescTable","[MyDescID] = " & Report!FieldB-ID)

My only 'sorting and grouping' choices seem to be 'FieldA-ID' and
FieldB-ID', so I get

I'd suggest that you get rid of the DLookUps and instead base the
report on a Query joining your main table to *two instances* of
MyDescTable, joining [FieldA-ID] (and, since you have a nonstandard
hyphen in the fieldname, *do* use the square brackets!) to MyDescID in
the first instance, and joining [FieldB-ID] to MyDescID in the second
instance. Pick up the two distributeion fields from the joined tables,
and sort by them as you wish.

John W. Vinson[MVP]

 

[Guest]

Thank you. Making a query with the ID linked twice, once from the parent and
once from the child, did the trick. I got just the records I needed and
sorted the way I wanted without using a Dlookup any more.
Regards,
- Cathy

I need to sort a report on the value in a control field, but I can't figure
out how to do that.

My table source for the report has only FieldA-ID (Parent) and FieldB-ID
(Child). A separate table has the description for FieldA-ID and FieldB-ID.

My report of the table is set up to display just the description in the
Parent header:
=DLookUp("[MyDesc]","MyDescTable","[MyDescID] = " & Report!FieldA-ID)

and just the description in the Child detail:
=DLookUp("[MyDesc]","MyDescTable","[MyDescID] = " & Report!FieldB-ID)

My only 'sorting and grouping' choices seem to be 'FieldA-ID' and
FieldB-ID', so I get

I'd suggest that you get rid of the DLookUps and instead base the
report on a Query joining your main table to *two instances* of
MyDescTable, joining [FieldA-ID] (and, since you have a nonstandard
hyphen in the fieldname, *do* use the square brackets!) to MyDescID in
the first instance, and joining [FieldB-ID] to MyDescID in the second
instance. Pick up the two distributeion fields from the joined tables,
and sort by them as you wish.

John W. Vinson[MVP]