 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / April 2008sorting question
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! >I have a subreport with an underlying query that sorts in ascending >order. The subreport also has a text box with the following control [quoted text clipped - 25 lines] >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.  SignatureMarsh MVP [MS Access] > Sorting a query is mostly useless for reports. Use the > report's Sorting and Grouping to sort the report's records. > > -- > Marsh > MVP [MS Access]- Hide quoted text - 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! >> Sorting a query is mostly useless for reports. Use the >> report's Sorting and Grouping to sort the report's records. [quoted text clipped - 14 lines] >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. SignatureMarsh MVP [MS Access] 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. :) 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]: [quoted text clipped - 21 lines] > [ShortAddress] and [LineDesc] regardless of where they appear in the > order. Do I need to maybe run another query...? >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 [quoted text clipped - 26 lines] >> [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. . SignatureMarsh MVP [MS Access] >> 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 [quoted text clipped - 54 lines] >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. SignatureMarsh MVP [MS Access] 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. >The design is: >[ParentID] > [ID]'s > [ShortAddress]'s > [LineDesc]'s [quoted text clipped - 41 lines] >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 SignatureMarsh MVP [MS Access] > 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: [quoted text clipped - 3 lines] > 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]) & " ● " & Max([LineDesc])) [ShortAddress] is actually a concantenation that uses " ● " 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! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.