Sort on Calculated Field in Report.

s_ojha

Board Regular
Joined
Oct 19, 2004
Messages
92
I have inserted expression/calculation as [A]++[C] in Total Field where [A], & [C] are fields formatted as Double with Fixed format and 02 decimal places in my Report. The problem is that I cannot sort on this calculated field. What I want is sort on this calculated field [Total] and incase of same number of two records then by [Date of Birth] field. i.e. I want to say ORDER BY [TOTAL],[Date of Birth].
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Often you need to use the *expression* that the calculated field is based on, not the field itself. That's why I made that suggestion

Denis
 
Upvote 0
Sir. I would just like to request you that when I am sorting on inserted textbox which has the expression [A]++[C], it does not give me the desired result in Report. The Record Source is Table. How and where can I insert sort order in Report i.e. the calculated Textbox[A]++[C], Date of Birth. I am sorry for that I mentioned as calculated Field, actually that was calculated expression.
 
Upvote 0
Try this instead.

Turn the control source for the report into a query (click the ... button at the end of the Control Source line, say Yes to the question about creating a query, add all the fields from the table, then create the calculated field in the query.
Give the field a name by using an expression like Total:[A]++[C]
Save the query when prompted.
Now go to the report and use that Total field in the report, instead of the calculated field that you created originally. You will be able to sort using the query field.

Denis
 
Upvote 0
Sir. You are great. I can use the same pattern to "Sort on Calculated expression in Form". But now the problem is that I want to add information for each record in "Remarks field". If I use the query I will not be able to store the information for each record in my original TABLE. How could I do that "Sort on calculated expression in Form" by Control Source as Query but at the same time to store the informations in Remarks Field of TABLE also.
 
Upvote 0
Using the query in the report:
As long as all the table fields are in the query, they can be used in the report.
You should also be able to sort or group on any of the fields.

Using the query data in the table:
As for storing data in the table, that shouldn't be a problem. If the query is editable, and a field from the table is in the query, any data changes will be stored in the table.

Denis
 
Upvote 0
Re: Sort on Calculated Field in Form.

Thanks infinite Sir. Actually sorry for not mentioning FORM in my subject. I used your suggestion in FORM and I was able to do all the requirement and stored the information in Main table. One problem in My FORM is that SIR I have to search the records in [A] field and make changes in [D] field. I am using the Find and Replace feature. Problem is that I search in [A] field and then edit in [D] field and then again click on [A] field and then make entry in [D] field.

Please tell me about any other trick or method to search data on a particular field i.e. by locking the field [A] to search so that cursor always remains in this field and as soon as the search value is found set the focus of the cursor in [D] field and after making changes in [D] field again set focus in [A] field to search.
 
Upvote 0
Re: Sort on Calculated Field in Form.

If you're searching, you may find a combo box easier to use if you are searching for complete fields. If so, look at this article.

Denis
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top