Sorting in a Report

[NewbyForAccess]

I'm trying to figure out the best way to sort descending on a report, but here's my problem. I have the fields I need included and they sum for each group. I created a textbox to calculate the percentage between the two fields; however, I need to sort on the data that's in the textbox and not from the two given fields. Is there a way to program Access to sort by this calculated textbox and not from any of the fields in my report? Hope someone can guide me where I need to be to get this figured out.

[pbaldy]

One solution is to move the calculation to the report's source query, then sort on that field. You can also sort on an expression in grouping and sorting.

[NewbyForAccess]

I tried everything I could think of to get this to sort including trying to use an expression, but because the item that I want to sort by is not a field, it's not allowing me to sort. The calculations are working perfectly, thank goodness. I think because this is a textbox with a calculation only, Access isn't recognizing it enough to use for sorting. If anyone can give me an idea on what code to use, I'd sure appreciate it. Obviously, what I've tried isn't working. I spent several hours on it yesterday and still can't get it to sort on this textbox. Thanks for the tip. Just wish it would have worked for me so I could move on. LOL

[pbaldy]

Did you try moving the calculation out to the query? Can you post the db here?

[NewbyForAccess]

Yes, I tried using the query but had no luck. What I have are four fields in my query. One is for customer, one for part number, and then two fields containing the piece count for the part numbers, one being for good pieces and one for scrap pieces. I wanted to calculate the percentage of scrap by using the following formula: =Scrap/Scrap + Good. Because I used "sum" for the two fields (scrap pcs and good pcs), I used the formula =[SumOfSCRAP PCS]/([SumOfGOOD PCS]+[SumOfSCRAP PCS]) in the control source for the textbox. The textbox is formatted for percent. When viewing the report, it's working perfectly for all calculations; however, I can't get it to sort in descending order.

[pbaldy]

Can you attach the db here?

[NewbyForAccess]

I don't think I can attach the db here because it's from my job. It all works except the sorting on a textbox field containing the formula for calculation. In my query, I only have the data and no calculation field. I am trying to avoid editing my tables and thought I'd be able to sort it through the report. Maybe I'm just not sure what code to use for the expression. Remember, I'm definitely a newby at programming the db. Haven't had a class in this for over 30 years and that was on dBase III. I'm showing my age now. LOL I'm trying to learn the code and have been working by trial and error for the most part. Thanks for whatever help I can get!

[pbaldy]

The same formula you have in the textbox should work in Sorting and Grouping, without the "=", like

[SumOfSCRAP PCS]/([SumOfGOOD PCS]+[SumOfSCRAP PCS])

[NewbyForAccess]

Paul, did I tell you that you're my hero! I can't believe this worked. I was thinking I had to give it a SortBy command or something similar so that's what I was trying to do. I just checked it out and was so totally shocked that I only needed to sort by my formula. I can't thank you enough for helping me with this. I've spent the past few days and several hours trying to figure it out. I am so totally happy right now! I know I've got lots to learn on the program and am so appreciative for the members of this forum who are so willing to help newbies like me. You are absolutely awesome! Thank you again and again!

[pbaldy]

Happy to help!

[NewbyForAccess]

Hey, Paul. It's the pest again. This is working perfectly, but I have a question I know you can answer if you don't mind. The command button for my report to print is based off of dates that are inserted on my main form, but the report pulls all the information for all my data and doesn't pull just from the dates specified. Is the reason it doesn't pull only by the criteria dates because I don't have a date field added to my report? This is the code I'm using for my command button:

Private Sub Command33_Click()
On Error GoTo Err_Command9_Click
Dim stDocName As String
Dim stLinkCriteria As String
If IsNull(Me.txtStartDate) Or _
IsNull(Me.txtEndDate) Then
MsgBox "Please enter date criteria."
Exit Sub
End If

DoCmd.OpenReport "Top Ten", acViewPreview, , strWhere
Exit_Command9_Click:
Exit Sub
Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub



Thanks again for any help you're willing to give me! I really appreciate this!

[pbaldy]

Well, there's nothing there that would filter anything, unless strWhere is set elsewhere. It's used here, but never given a value.

[NewbyForAccess]

I had the code in for my dates being pulled from my main form. I've since removed them because we decided at work today to keep an ongoing record so no dates will be used. The report is working perfectly! Thank you so much for your help. I plan to study more from your web site. I have much to learn!

[pbaldy]

Happy to help!

[NewbyForAccess]

Okay, everything is working fine with this except one big issue that I ran across today. Now what I'm seeing is in the detail section of my report where I'm calculating my percentage in a textbox with this formula:

[scrap pcs]/[good pcs]+[scrap pcs]

It works except now I see that I'm getting a #Num value in my scrap % textbox when both scrap pcs and good pcs contain the values of 0. I'd like to get this error code removed without affecting the calculations for all other records in the report. I know it's because of the division by 0 issue. I've tried every combination of things I've seen posted on the net and can't find anything that seems to work for me. I was so sure I could figure this one out since I'm very familiar with this in Excel, but nothing is working. Any ideas?