Report Sort order and Labels

[MNNovice]

Hi.

1. The report rptGrantList is based on tables where I have the field GrantNo (text data type) sorted to ascending order. However, my report is not listing the grant numbers in an ascending order. What's wrong? Is it because the field has text data type? If so, how can I get to be sorted?

2. The main report rptECHO include 3 separate sub reports (srptAP, srptPay, srptAllocation). Not all the ECHOs have data for these sub reports. In which case I would like to suppress the labels. That is, instead of showing the label "AP Expenses" and zero data I would rather not to show the label "AP Expenses" at all. Is it possible in ACCESS 2003?

Thanks.

[NeoPa]

1. Reports define their own sort order, regardless of the defined order of the incoming data.
   See Sorting and Grouping from the View menu.
2. I'm pretty sure it is.
   There is an On No Data event which can be used to trigger code setting the subreport visibility to False or True depending on the new state.

[Megalog]

For #1, you can also force the report to sort by GrantNo using it's On Load event. It can always be re-sorted to something else afterwards. This is usually a good idea if it gets sent straight to a printer, have multiple end users that are clueless with the menus, etc..

Private Sub Report_Load()
     Me.OrderBy = "[GrantNo]"
     Me.OrderByOn = True
End Sub

Edit:
I forgot to add, you said GrantNo is a text type field? If that's the case, then it wont sort numbers properly. For example, 1, 2, 5, 15, 20, would be sorted as 1, 15, 2, 20, then 5. If this is the case, then in your report's recordsource modify the query to include another field that is basically a long integer type conversion of the GrantNo field (look up the "CLng" function in the help file). Then replace the "GrantNo" in the code above, with the new field name you created.

[NeoPa]

@Megalog
I found this curious.

I can see that sorting by something other than the reports default sort order may be required sometimes, and this is a good technique to use for that. I can't see why it would ever be preferable to designing the sort order in the standard way where you can most easily find it again. Am I overlooking something here Mega?

[MNNovice]

NeoPa:

Reports define their own sort order, regardless of the defined order of the incoming data.
See Sorting and Grouping from the View menu.

My report didn't include a header for GrantNo. I added one and then used ascending order under Sorting & Grouping. It's working now.

There is an On No Data event which can be used to trigger code setting the subreport visibility to False or True depending on the new state.

I do not know how to right the code that will go into the private sub...

Thanks.

[NeoPa]

@MNNovice

1. Open the report for editing.
2. Show properties for the report.
3. Look for On Load.
4. Select [Event Procedure] from the list.
5. Click on the ellipsis button to the right.
6. Paste in the code posted.

[MNNovice]

Hummmm!!

I don't see any code posted by you on onNoData. Am I missing something? I knew about the steps 1 through 5, I need to know where are the codes that's is described in step 6 (LOL).

Thanks. M

[NeoPa]

I thought your post #5 was in reference to MegaLog's post #3. That is where the code is that I thought you were talking about.

NB. My post referred to the On Load event rather than the On No Data one.

[Megalog]

@NeoPa
Neo..
Sometimes I get users going into a db and setting custom sorts on reports, saving them, and then the next user get's stuck with that custom sort. This way I know for sure, by using vba, that the report initially get's shown the way it's intended to. If it's a distributed MDE/ACCDE, I dont have to worry about it too much, but if it's a standalone db that gets used by random users once in a while, I like to make sure it stays the way it's supposed to be.

[NeoPa]

That sort of makes sense, but logically you could use the code simply to test for and remove any custom sorts.

This way you could have what you need, without your sorting being specified in a non-standard way.

Does that make sense?

[MNNovice]

NeoPa:

. The main report rptECHO include 3 separate sub reports (srptAP, srptPay, srptAllocation). Not all the ECHOs have data for these sub reports. In which case I would like to suppress the labels. That is, instead of showing the label "AP Expenses" and zero data I would rather not to show the label "AP Expenses" at all. Is it possible in ACCESS 2003?

I'm pretty sure it is.
There is an On No Data event which can be used to trigger code setting the subreport visibility to False or True depending on the new state.

Above is my question and your answer. May I know how to write the VBA code for the On NoData? I tried these and didn't succeed:

If Me.srptEchoPayDetail.Report.HasData = True Then 
    rptEchoPaySum Lable.Visible = True 
Else 
    Me.srptEchoPayDetail.Visible = False 
    rptEchoPaySum Label.Visible = False 
End If

Thanks.

[NeoPa]

I'm trying to test out a theory, but I cannot find in your project where rptECHO is ever run :S

NB. I'm working with the last version you emailed.

[MNNovice]

NeoPa

I'm trying to test out a theory, but I cannot find in your project where rptECHO is ever run :S

I was tied up with too many meetings yesterday afternoon and didn't get around to answering to you.

Well, I don't know what does that ":S" mean in your question. But I am e-mailing you the latest version of my DB. Please use the switchboard and select:

Main menu
Report Menu
ECHO Detailed Report
It should open rptECHO with all the Echos done todate.

Now these are my objectives:

1. Arrange this report as such that I have the report by ECHO number by category (A/P, Payroll, Allocation Cost etc) and sorted by Grant Number. This last step, i.e., sorting by GrantNo is not working for me.

2. Within each Echo number I would like a summary total by each category. For example, ECHO 09-062 / Total A/P Expenses / Total Payroll Expenses / Total Allocation Expenses. I succeeded doing it for the entire report but not for each ECHO. If you look at the report footer, you will know what I mean.

3. Now I want a separate report similar to this one but only arranged differently. Let's call it a fund report. So I will have a report that will arrange the data by fund and not by Echo number. for example,

Fund 872
Echo No        Sub Class   Project No        A/P                Pay     Allocation
09-062           T217        63740         $382,907             0            0
09-063
09-064

You get the idea. So far I have not made any headway with this one.

Hope I am making myself understood. Attached is the latest DB for your review. Looking forward to your direction.

Many thanks.

[NeoPa]

M,

I may get on to other questions later (although throwing a whole bunch in at once is generally not such a good idea), but first I will attempt to answer this one properly if I can.

I will download the database this evening but won't be home till quite late. If I get the time I will look at some of your other questions.

[Megalog]

I took a peek at the db you uploaded, and as far as your original sort problem goes, it seems like a simple fix.

You've got 3 subreports within the main report, one for each category. You have to sort by GrantNo within each of the subreports.

Also.. I hope that isnt live data you have in that db you uploaded. If so I would recommend putting in some dummy values.

[MNNovice]

Mega:

Thanks. My report is now sorted by grant number within each category.

[MNNovice]

NeoPa:

Please ignore question 1 in posting #13. Help me with question 2 (same posting). Let's deal with question #3 at a later time.

Thanks.

[Megalog]

It looked like each subreport/category was already being summarized.. am I misreading what you're asking for? You want all the categories to be summed in one grand total per echo?

[NeoPa]

@MNNovice
Let me say again, it is not helpful to throw multiple questions along all at once.

I am working on none of these, but at trying to find a way to disable the showing of a subreport when there is no data (as you asked earlier).

You can see from other responses that asking multiple questions before you have the earlier answers just causes confusion everywhere. That is the main reason we have rules about one subject per thread. We can be flexible about it when it makes sense, but throwing in a bunch of questions all at once like this doesn't. It just causes there to be various conversations crossing over each other in the thread, to everyone's confusion.

I will update when I make any progress. As I said I was out tonight, but I have managed to dl the database so I will remove it if/when you request it.

Have a good night & I'll catch up tomorrow if I can.

[MNNovice]

NeoPa:

I can understand the rigidity of such rule of having one question per thread. But sometimes we have to be flexible with rules. I am sure you will agree taht if I have multiple problems with one report and I were to submit these questions separately then I will run into describing the details of each table, form, report etc. multiple times. Otherwise I will face the same old question, "It is not clear from your question ...we need more details on the structure of your table...etc. etc.

But I respect your rules and shall try to follow it. If you could move/delete these other questions, feel free to do so. I will post them separately.

And thanks for your time. M

[MNNovice]

It looked like each subreport/category was already being summarized.. am I misreading what you're asking for? You want all the categories to be summed in one grand total per echo?

Mega:

No you have not misread. Within each Echo I want a total like this

ECHO #09-062
AP           $xxxxx
Payroll      $xxxxx
Alloc Cost   $xxxx
 TOTAL 09-062 $xxxx

This is what I was referring to vide question #2 posting 13. I was able to pull the Grand Total (for all ECHOs) for the entire report but not for each of the ECHO.

Hope this makes sense. Thanks.

[NeoPa]

@MNNovice
We are not enforcing the the rules rigidly in your case M. Principally for the reasons you explain here.

However, it is still not sensible to throw multiple questions all in together at a single time, for reasons that I hope are now clear after my earlier post. It puts extra strain on those trying to help, and causes much confusion amonst everyone who posts.

I appreciate that you're under time pressure but, while we'd like to help as much as possible, there are limits to what should be expected. That's where I come in as an administrator. My responsibility (as an admin or moderator) is to ensure things don't get too complicated and difficult for the experts to deal with.

Ultimately this benefits the members asking questions as much as it does the experts (Trust me on this - I have some experience in the matter. I've seen the threads that simply get left behind because the question is framed in too complicated a way or it just gets that way after a while).

No deletions required at this point. We'll just try to catch up with everything and move on from here if we can.

[MNNovice]

NeoPa:

Thanks for the clarifications. M

[NeoPa]

@MNNovice
Sorry, I missed explaining this earlier.

This is a :confused: smiley. It's just a graphic and easily seen illustrater of what I was trying to say.

I'm looking again at the problem and hope to post later with something helpful (1st helpful one for a while I know).

[NeoPa]

This was not as easy as I'd assumed (By the way I'm still answering the question posed in the original post and repeated in post #5), but I managed to find another way when I failed to find a workable way with the NoData event. There is something in the help on the .HasData property which points the right way. I think the following code will work correctly for you though (It worked perfectly on my copy of your database) :

Option Compare Database
Option Explicit
 
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    With Me.APExpenses
        Me.[rptEchoAPSum Label].Visible = .Report.HasData
        .Visible = .Report.HasData
    End With
    With Me.PayExpenses
        Me.[rptEchoPaySum Label].Visible = .Report.HasData
        .Visible = .Report.HasData
    End With
    With Me.AllocationCost
        Me.[Allocation Cost Label].Visible = .Report.HasData
        .Visible = .Report.HasData
    End With
End Sub

PS. I should add that this code is in the Format event of the section in rptECHO called [ECHONo Header].

[NeoPa]

When you have checked this, let me know if it works for you (obviously) but also tell me which of the questions wants answering next.

I will always try to answer one at a time only. It's much quicker that way.

[MNNovice]

NeoPa:

Thank you for the codes. I will keep you posted if I succeed in following your instructions.

Here is one question I could use your help.

For the rptECHO, I would like a sub total of each category for each individual ECHO.

For example,
ECHO 09-062
Total A/P Expenses
Total Payroll Expenses
Total Allocation Costs
TOTAL 09-062

I succeeded pulling the total for the entire report but not for each ECHO. I attempted to do this by creating an ECHOID Footer but the formula I used for the Report footer, didn't work.

If you have time to help me with this question, that will be great. Thanks.

[NeoPa]

That'll be my next point of focus M.

I will look at it from home of course as that is where my copy of your database is kept.

This evening is my first one at home since Monday though, so I should be able to get something sorted. I assume I'm following the same steps as detailed in post #13 for showing the correct report etc?

[MNNovice]

NeoPa:

Thanks for your tips on post #25. The codes worked perfectly for me. Thank you. If morning shows the day then I have hope for today. It was a terrible day yesterday until at the end when I resolved the union query issue with the help from Denburt.

Thank you all. M

[NeoPa]

@MNNovice
Well, something strange going on here. It seems to me that you already have what you're asking for. I found it in the ECHOID Footer (GroupFooter1) sections of your subreports.

If what you have here is not what you need for some reason, or the calculations are not correct, then you need to be clearer as to what you're after. If it's a calculation problem then I need to know what figures to expect.

If it's layout then you need to be clear and precise about what you want. I'm here and ready. I'd like to help, but I can't without an understanding of what you want.

[MNNovice]

NeoPa:

Friday was my day off and here I am on Monday morning responding to your questions. Let me try.

rptECHO page 2 ends with data on ECHO No. 09-062. I would like a summary of expenses for ECHO No. 09-062 before the report displays data on the next Echo. Therefore, the page 2 will show a summary like I have for the entire report on the last page.

Echo No.:  09-062
Ap Exp:  1,097,283
Pay Exp.:            0
Alloc. Cost:         0
Total Exp  1,097,283

I would like this summary to be repeated for each of the following ECHO. I don't believe I have this figure on my report. This is what I tried to point out when I said that I attempted to do this by creating an ECHOID Footer using the same formula I used for the Report footer, but it didn't work.

Hope this clarifies any confusion I may have inadvertently caused. Thanks.

[NeoPa]

Let me look at this again when I get home then, with this latest post to hand, and see what I can make of it.

I understand that it can be very difficult to express these things sometimes, especially if you don't think naturally like a database person (we'll get you there some day I'm sure), so if my posts sound curt sometimes, then I'm sorry. I have let my frustration show through.

I should know better than to expect everyone to think the way I do, but I still get frustrated when people don't.

If, when I look at the database along with the latest explanation, I am still a bit lost, are you happy if I try to call you to discuss?

[MNNovice]

Definitely Yes!! :S. Muchas gracias

[NeoPa]

That's good. We'll see how it goes then.

[NeoPa]

If you choose View / Sorting and Grouping and look at the Field/Expression ECHONo you will see that the Group Header is set to Yes, but that the Group Footer is set to No.

You need to set this to Yes and put in some controls that reference the totals.

This seems very much like a question you have in another thread (IIf Statement for Null Value).

If not, then I will look into it more deeply for you.

[NeoPa]

I did try to call by the way, but there was no answer.

Probably not necessary anyway, but just so you know I did try.

[MNNovice]

As I added a footer for the EchoNo, the summary came out right. Don't know why didn't I think of it. I kept adding ECHOID footer...silly me.

Now everything is working fine with this report. Thank you very much.

Sorry to have missed your call. Last evening I had to make an emergency run to the grocery store. Hopefully we get to talk on another day.

M

[NeoPa]

I'm sure we will.

I'll let this thread lie now then.

If you have any others that you are still struggling on let me know. Things seem to be very quiet at the moment and I have more time than usual.

[MNNovice]

NeoPa:

Before you do so - one question. I used the same formula to calculate the grand total for the entire report and added it to the report footer. But it's not working. All I am getting the summary total for the last Echo. How may I fix this problem? I mean how do I get the total for the entire report?

Thanks.

[NeoPa]

Basically, you use =Sum() with a reference to the controls that are the totals of each ECHONo.

I expect this would be the controls I was referring to in post #35.

Does this help?

[MNNovice]

NeoPa:

The name of the AP expense control in EchoNo footer is called txtAPEcho.

When I used

=Sum(txtAPEcho)

Of course I got an error message. And I don't know how you would put a summation formula to a textbox on a report footer referring to a groupfooter that belongs to a sub report. Hummmm! (LOL)

[NeoPa]

As this refers to an area that you've added since you last sent me a copy of your database I suggest sending the latest version would help me to see more easily where and how to put it. Email is fine.

That way I can use the proper names and show you exactly how to do it.

[MNNovice]

Will do. Many thanks.

[NeoPa]

I have received the latest database thanks :)

I expect I shall be able to give this some attention this evening (although the England qualifier against Andorra is on live tonight). I'll update with what I can find.

[MNNovice]

No problem. No hurry. Enjoy your evening. I know you are quite busy at work.

[NeoPa]

I've checked now. There is good news and bad news :(

The good news is that you did it perfectly already.

The bad is that my suggestion was rubbish.

I'm sorry. It seems I got it all wrong. I don't understand how/why yet. I will have to look into it further. It may take some time. maybe someone else who has more experience with sub-reports (I've only ever designed one report that used a sub-report) can jump in.

[NeoPa]

It seems that aggregating (totalling etc) in a report footer (and probably elsewhere in a report too) can only work with the underlying fields of the Control Source.

This section from online help may prove helpful.

[MNNovice]

NeoPa:

Thanks for the info. I believe I have a copy of the online help . Nevertheless I will read it again and shall let you know.

Provided you have time, can you please check this question for me? Calculate with conditions

By the way congrats on a great victory by England last night. Was that Beckham I saw there? Argentina was a huge disappointment but I am happy for Ecuador. I am fan of Brazil though...

[NeoPa]

@MNNovice
You should have access to it. It's online and available to all. A visit wouldn't hurt, but it's not all basic stuff. There are some more complicated situations there. Unfortunately nothing that directly matches your requirements.
@MNNovice
Thanks. Yes Beckham played started for England for the 100th time yesterday, though he has more caps than that of course. Still never scored for England at Wembley though :(

Who doesn't like to watch Brazil. They're always worth a watch.
@MNNovice
Zipping over there for a quick look now.