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]

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.

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]

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

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]

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?

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.