sorting question

[jlute]

I have a subreport with an underlying query that sorts in ascending
order. The subreport also has a text box with the following control
source:
="Facility ID/Line ID: "+IIf([Forms]!
[frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] Is Not
Null,+[ShortAddress] & " / " & [LineID] & " " & [LineDesc])

The text box displays properly ONLY if the FIRST record in the
ascending order contains a value in [ShortAddress]. If that's a little
fuzzy then maybe I can illustrate:

ID (ascending) | ShortAddress | LineDesc
100123 | 123 Any Town, USA | 02 - Thermoform
200345 | |
300444 | |
In this case, the text box will properly display [ShortAddress] and
[LineDesc].

In the next case, the text box will NOT properly display:
ID (ascending) | ShortAddress | LineDesc
200123 | |
300345 | 123 Any Town, USA | 02 - Thermoform
400444 | |

Obviously, this is because the ascending order has placed the record
with [ShortAddress] second in order.

How can I change the code to display the record with [ShortAddress]
and [LineDesc] regardless of its order?

Thanks in advance for your help!

 

[Marshall Barton]

I have a subreport with an underlying query that sorts in ascending
order. The subreport also has a text box with the following control
source:
="Facility ID/Line ID: "+IIf([Forms]!
[frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] Is Not
Null,+[ShortAddress] & " / " & [LineID] & " " & [LineDesc])

The text box displays properly ONLY if the FIRST record in the
ascending order contains a value in [ShortAddress]. If that's a little
fuzzy then maybe I can illustrate:

ID (ascending) | ShortAddress | LineDesc
100123 | 123 Any Town, USA | 02 - Thermoform
200345 | |
300444 | |
In this case, the text box will properly display [ShortAddress] and
[LineDesc].

In the next case, the text box will NOT properly display:
ID (ascending) | ShortAddress | LineDesc
200123 | |
300345 | 123 Any Town, USA | 02 - Thermoform
400444 | |

Obviously, this is because the ascending order has placed the record
with [ShortAddress] second in order.

How can I change the code to display the record with [ShortAddress]
and [LineDesc] regardless of its order?

Sorting a query is mostly useless for reports. Use the
report's Sorting and Grouping to sort the report's records.

 

[jlute]

Sorting a query is mostly useless for reports.  Use the

report's Sorting and Grouping to sort the report's records.

Hi, Marsh!

I see I wasn't very clear. I meant to say that the query sorts
ascending NOT because it has a sort order but because the ID is the
primary key and therefore automatically sorts ascending.

I took another look at the subreport and sorted [ShortAddress]
descending so that any ID's with null values are shoved to the bottom.
This enables the text box to properly appear however the ID's are
thrown out of order. To resolve that I sorted the ID's ascending. This
results in the ID with a value in [ShortAddress] to be first followed
by all other ID's in ascending order which is not exactly what I'd
like.

This seems impossible to resolve. I'm back to square one:
The text box displays properly ONLY if the FIRST record in the
ascending order contains a value in [ShortAddress].

Any other ideas? I'm about to throw in the towel!

 

[Marshall Barton]

Sorting a query is mostly useless for reports.  Use the
report's Sorting and Grouping to sort the report's records.

I see I wasn't very clear. I meant to say that the query sorts
ascending NOT because it has a sort order but because the ID is the
primary key and therefore automatically sorts ascending.

I took another look at the subreport and sorted [ShortAddress]
descending so that any ID's with null values are shoved to the bottom.
This enables the text box to properly appear however the ID's are
thrown out of order. To resolve that I sorted the ID's ascending. This
results in the ID with a value in [ShortAddress] to be first followed
by all other ID's in ascending order which is not exactly what I'd
like.

This seems impossible to resolve. I'm back to square one:
The text box displays properly ONLY if the FIRST record in the
ascending order contains a value in [ShortAddress].

Where is this text box? It sounds like it's in the report
(or page) header. I would expect it to behave if it were in
the detail section.

The sorting issue still needs to be addressed. You can not
rely on the query sorting without using the Ordery By
clause. And even if you do that, the report will very
likely ignore it. From your explanation, you should specify
the ID field at the top of the report's Sorting and
Grouping.

 

[jlute]

Hi, Marsh! Thanks for the response!

Where is this text box?  It sounds like it's in the report
(or page) header. I would expect it to behave if it were in
the detail section.

It's in the detail.

The sorting issue still needs to be addressed.  You can not
rely on the query sorting without using the Ordery By
clause.  And even if you do that, the report will very
likely ignore it.  From your explanation, you should specify
the ID field at the top of the report's Sorting and
Grouping.

I don't think I'm being very clear - sorry! Maybe I need to provide a
few more details that may or may not be relevant to the issue.

The query behind the subreport filters for [ShortAddress] and
[LineDesc]. I have a criteria form where I select these values and
then execute the report/subreport. The subreport returns many ID's
that are related to the main report's ID. Some of the related ID's do
NOT have [ShortAddress] or [LineDesc] values therefore the only way I
can get the text box to appear is if I sort the related ID's in
descending order by [ShortAddress] so that the ID's with
[ShortAddress] values are listed at the top. Like I said, while this
helps the text box display it results in the related ID's to be out of
numerical order. Allow me to refine my previous examples.

Example A:
In this case, the text box WILL display [ShortAddress] and [LineDesc]:
ID (ascending).| ShortAddress............| LineDesc
100123...........| 123 Any Town, USA...| 02 - Thermoform
200345...........|.................................|
300444...........|.................................|

Example B:
In this case, the text box will NOT display:
ID (ascending).| ShortAddress............| LineDesc
100123...........|.................................|
200345...........| 123 Any Town, USA...| 02 - Thermoform
300444...........|.................................|

Note that in the above examples that the ID's are in ascending
numerically order. In order for the text box to display in Example B I
need to sort [ShortAddress] in descending order like this:
ID (ascending).| ShortAddress (descending).| LineDesc
200345...........| 123 Any Town, USA...........| 02 - Thermoform
100123...........|..........................................|
300444...........|..........................................|

I guess the trick here is to get the text box to display
[ShortAddress] and [LineDesc] regardless of where they appear in the
order. Do I need to maybe run another query...?

In short this is part of a very complex design in which ID's have many
related ID's that may be related to many [ShortAddress]'s and many
[LineDesc]'s. BTW Dirk Goldgar actually helped me with my design
efforts. I'm sure you're aware of Dirk's infamous prowess and bridled
insanity.

 

[jlute]

I just wanted to clarify that these examples are QUERY results and NOT
report results. I don't want the report to display [ShortAddress] and
[LineDesc] for each ID - it just needs to display once hence the text
box.

 

[Marshall Barton]

Where is this text box?  It sounds like it's in the report
(or page) header. I would expect it to behave if it were in
the detail section.

It's in the detail.

The sorting issue still needs to be addressed.  You can not
rely on the query sorting without using the Ordery By
clause.  And even if you do that, the report will very
likely ignore it.  From your explanation, you should specify
the ID field at the top of the report's Sorting and
Grouping.

I don't think I'm being very clear - sorry! Maybe I need to provide a
few more details that may or may not be relevant to the issue.

The query behind the subreport filters for [ShortAddress] and
[LineDesc]. I have a criteria form where I select these values and
then execute the report/subreport. The subreport returns many ID's
that are related to the main report's ID. Some of the related ID's do
NOT have [ShortAddress] or [LineDesc] values therefore the only way I
can get the text box to appear is if I sort the related ID's in
descending order by [ShortAddress] so that the ID's with
[ShortAddress] values are listed at the top. Like I said, while this
helps the text box display it results in the related ID's to be out of
numerical order. Allow me to refine my previous examples.

Example A:
In this case, the text box WILL display [ShortAddress] and [LineDesc]:
ID (ascending).| ShortAddress............| LineDesc
100123...........| 123 Any Town, USA...| 02 - Thermoform
200345...........|.................................|
300444...........|.................................|

Example B:
In this case, the text box will NOT display:
ID (ascending).| ShortAddress............| LineDesc
100123...........|.................................|
200345...........| 123 Any Town, USA...| 02 - Thermoform
300444...........|.................................|

Note that in the above examples that the ID's are in ascending
numerically order. In order for the text box to display in Example B I
need to sort [ShortAddress] in descending order like this:
ID (ascending).| ShortAddress (descending).| LineDesc
200345...........| 123 Any Town, USA...........| 02 - Thermoform
100123...........|..........................................|
300444...........|..........................................|

I guess the trick here is to get the text box to display
[ShortAddress] and [LineDesc] regardless of where they appear in the
order. Do I need to maybe run another query...?

In short this is part of a very complex design in which ID's have many
related ID's that may be related to many [ShortAddress]'s and many
[LineDesc]'s. BTW Dirk Goldgar actually helped me with my design
efforts. I'm sure you're aware of Dirk's infamous prowess and bridled
insanity.

The only new information I can find in there is:
"this is part of a very complex design in which ID's
have many related ID's that may be related to many
[ShortAddress]'s and many [LineDesc]'s"

That leaves me wondering what:
"many related ID's that may be related to many
[ShortAddress]'s and many [LineDesc]'s"
means in terms of your (sub) report. If you are describing
a relationship between the main report and the subreport
then I don't see how it is important to the problem in the
subreport. If you are desctibing something within the
subreport, I do not understand what you are saying.

Regardless of what you have said, I can not imagine this
issue being a "sorting problem".

It almost seems that there is a "complex" mechanism relating
the IDs to the other fields that goes beyond a record source
providing fields to a coherent detail section. My confusion
stems from your statement that the problem is with the first
record not having a value for the address. That is
completely inconsistent with the normal behavior of a report
where ***each detail's displayed data can only depend on
fields in the same detail record***. If there is some
mechanism beyond the simple text box expression you posted
earlier, then that is where I would look to find an
explanation for the strange behavior.

 

[Marshall Barton]

I just wanted to clarify that these examples are QUERY results and NOT
report results. I don't want the report to display [ShortAddress] and
[LineDesc] for each ID - it just needs to display once hence the text
box.

Example A:
In this case, the text box WILL display [ShortAddress] and [LineDesc]:
ID (ascending).| ShortAddress............| LineDesc
100123...........| 123 Any Town, USA...| 02 - Thermoform
200345...........|.................................|
300444...........|.................................|

Example B:
In this case, the text box will NOT display:
ID (ascending).| ShortAddress............| LineDesc
100123...........|.................................|
200345...........| 123 Any Town, USA...| 02 - Thermoform
300444...........|.................................|

Note that in the above examples that the ID's are in ascending
numerically order. In order for the text box to display in Example B I
need to sort [ShortAddress] in descending order like this:
ID (ascending).| ShortAddress (descending).| LineDesc
200345...........| 123 Any Town, USA...........| 02 - Thermoform
100123...........|..........................................|
300444...........|..........................................|

I guess the trick here is to get the text box to display
[ShortAddress] and [LineDesc] regardless of where they appear in the
order. Do I need to maybe run another query...?

That does seem to be the problem. I can't say if another
query can help or not because I have no idea where the data
for the text box is comming from. It seems obvious to me
that it is not coming from the fields in the detail
section's records.

The fact that it does produce the correct results when the
first detail has non-null values tells me that something
like a DLookup or ?? is being used to retrieve the values
for the text box.
..

 

[jlute]

You're a trooper, Marshall!

The only new information I can find in there is:
   "this is part of a very complex design in which ID's
     have many related ID's that may be related to many
     [ShortAddress]'s and many [LineDesc]'s"

So you ARE familiar with Dirk's bridled insanity? He was equally
confused when I approached him about this!

That leaves me wondering what:
        "many related ID's that may be related to many
     [ShortAddress]'s and many [LineDesc]'s"
means in terms of your (sub) report.

The design is:
[ParentID] > [ID]'s > [ShortAddress]'s > [LineDesc]'s

The subreport is returning [ID]'s > [ShortAddress]'s > [LineDesc]'s
according to the form filter.

The filter is designed to return the desired [ShortAddress] and
[LineDesc] for a given [ID] and [ParentID]. Let's say that the
[ParentID] is "123" with 4 related [ID]'s. 100123 is unique to
[ShortAddress] 123 Any Town, USA and [LineDesc] 02 - Thermoform.
100200 is unique to [ShortAddress] 244 Somewhere, USA and [LineDesc]
01 - Thermoform:
ID (ascending).| ShortAddress............| LineDesc
100123...........| 123 Any Town, USA...| 02 - Thermoform
100200...........| 244 Somewhere, USA| 01 - Thermoform
200345...........|.................................|
300444...........|.................................|

Let's say we want to filter for [ID]'s unique to [ShortAddress] 123
Any Town, USA and its [LineDesc] 02 - Termoform. We plug in the values
in the criteria form and the report returns:
ID (ascending).| ShortAddress............| LineDesc
100123...........| 123 Any Town, USA...| 02 - Thermoform
200345...........|.................................|
300444...........|.................................|

So the filter and subreport returns all [ID]'s unique to
[ShortAddress] 123 Any Town, USA as well as all [ID]'s that are NOT
unique to any [ShortAddress].

I hope this helps describe the complex mechanism. Everything works
properly and always has. I've just gotten to the point where I'd like
a text box to display the [ShortAddress] and [LineDesc] ONCE as a
label for the entire group of [ID]'s regardless that not all of them
are unique to any particular [ShortAddress].

I did give you some bad info before. I actually do have the text box
in the subreport's header. Sorry about that. Not sure how I got turned
around on that. Of course, depending on where it's placed in the
detail it will display either above or below the record. Sorry if this
caused any confusion!

Ultimately, I think what I'm trying to do with the text box is
impossible. I should probably just place it in the detail and have it
appear accordingly regardless of how ugly that will be.

 

[Marshall Barton]

The design is:
[ParentID] > [ID]'s > [ShortAddress]'s > [LineDesc]'s

The subreport is returning [ID]'s > [ShortAddress]'s > [LineDesc]'s
according to the form filter.

The filter is designed to return the desired [ShortAddress] and
[LineDesc] for a given [ID] and [ParentID]. Let's say that the
[ParentID] is "123" with 4 related [ID]'s. 100123 is unique to
[ShortAddress] 123 Any Town, USA and [LineDesc] 02 - Thermoform.
100200 is unique to [ShortAddress] 244 Somewhere, USA and [LineDesc]
01 - Thermoform:
ID (ascending).| ShortAddress............| LineDesc
100123...........| 123 Any Town, USA...| 02 - Thermoform
100200...........| 244 Somewhere, USA| 01 - Thermoform
200345...........|.................................|
300444...........|.................................|

Let's say we want to filter for [ID]'s unique to [ShortAddress] 123
Any Town, USA and its [LineDesc] 02 - Termoform. We plug in the values
in the criteria form and the report returns:
ID (ascending).| ShortAddress............| LineDesc
100123...........| 123 Any Town, USA...| 02 - Thermoform
200345...........|.................................|
300444...........|.................................|

So the filter and subreport returns all [ID]'s unique to
[ShortAddress] 123 Any Town, USA as well as all [ID]'s that are NOT
unique to any [ShortAddress].

I hope this helps describe the complex mechanism. Everything works
properly and always has. I've just gotten to the point where I'd like
a text box to display the [ShortAddress] and [LineDesc] ONCE as a
label for the entire group of [ID]'s regardless that not all of them
are unique to any particular [ShortAddress].

I did give you some bad info before. I actually do have the text box
in the subreport's header. Sorry about that. Not sure how I got turned
around on that. Of course, depending on where it's placed in the
detail it will display either above or below the record. Sorry if this
caused any confusion!

Ultimately, I think what I'm trying to do with the text box is
impossible. I should probably just place it in the detail and have it
appear accordingly regardless of how ugly that will be.

So, I was right, the text box problem is because it is in
the subreport's header section and the effect you are seeing
is exactly as expected. With all that in mind, I think your
original question boils down to: How to display a specific
detail's value in the report header.

If all of the other details have Null in the relevant
fields, then you can use the Max function to find the
non-Null value:

="Facility ID/Line ID: " +
IIf([Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs]
Is Not Null, Max([ShortAddress]) & Max([LineID]) & " / " &
" " & Max([LineDesc]))

This approach can not isolate the ID of the detail with the
related data because there are non-Null values in all the
records.

If that expression does not meet your needs, then maybe you
can use Sorting and Grouping to sort the non-Null entry
first. The sort field/expressions would be like:

=IIf(ShortAddress Is Null, 2, 1) Ascending
ID Ascending

 

[jlute]

So, I was right, the text box problem is because it is in

the subreport's header section and the effect you are seeing
is exactly as expected.

Yeah. So sorry about that confusion!

With all that in mind, I think your
original question boils down to: How to display a specific
detail's value in the report header.

If all of the other details have Null in the relevant
fields...

Actually, other details can have values BUT they will be all the same
as what is filtered for.

...then you can use the Max function to find the
non-Null value:

="Facility ID/Line ID: " +
IIf([Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs]
Is Not Null, Max([ShortAddress]) & Max([LineID]) & " / " &
" " & Max([LineDesc]))

Well, you're obviously an MVP for a reason! No way would I have gotten
that on my own. Thanks for sticking this out with me! I've learned yet
another "trick" in a long line of tricks!

I modified it just a tad to:
=+IIf([Forms]![frmQueryFGProcessingFacIDsLineIDs]!
[cbProfilesAssocsFacIDs] Is Not Null,Max([ShortAddress]) & " / " &
Max([LineID]) & " $B!|(B " & Max([LineDesc]))

[ShortAddress] is actually a concantenation that uses " $B!|(B " so I
thought I'd throw it in between [LineID] and [LineDesc] in order to be
consistent. I also removed "Facility ID/Line ID:" as it is obvious
when viewed.

WOW!!! I keep getting reminded that nothing appears to be impossible
with Access.

I just checked it against all scenarios and it returns properly. A
super, big thanks to you! This has been one of the most agonizing
problems I've ever tried to resolve and you did it with a snap!

Thanks!