Hi experts,
In ds form i have some calculated columns(either by functions, either they are clalculated as the sum of other fields) and i want to allow the user to sort ascendig or descending, but these buttons are disabled? Is there any work around or just because these colums are calculated when the form is open and their values are not saved anywhere i cannot make a sorting based on them?
Thanks for any help
Manolis
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
A slightly different way, using a sort on a total column as an example:
Private Sub Form_Load()
OrderBy = "[Total Column Name]"
OrderByOn = True
End Sub
where grouping is required, you must do a group by before you can sort the results
see also this link: http://www.microsoft.com/o
The sort buttons are only disabled with you create expressions directly in the form. For example, if you have, in txtTotal:
= Quantity * UnitPrice
That expression is evaluated by the Form engine, not by Jet, so that it cannot use it as sort criteria. However, if you include it in the query, it will work. Click on the builder button to create a query, include the (table name).* in order to have all fields and add this calculated field:
Total: Quantity * UnitPrice
Return to your form and replace the expression with 'Total'. This way, Jet know what the field contains and will happily sort by it.
Cheers!
(°v°)
As long as it's a simple calculation harfang's solution will work, however the calculated field can't be updated for obvious reasons. t will however reflect a change to the qty or price.
GroupBy queries with a Sum() or other function however won't allow changes.
You can always set the field properties to lock it for update and to be skipped when tabbing.
Nic;o)
Ah, I see. As Nico implied [hi, btw!], you can edit data in a query. As a matter of fact, that is the only way to edit data comfortably (and the only way to edit the table "directly" is to do so from code!).
When you create a form based on, say, Table1, Access creates a very simple select query for you, and uses that. Something like:
SELECT * FROM Table1;
Instead, you will use something like this (using the same expression as above):
SELECT Table1.*, Quantity * UnitPrice As Total FROM Table1;
Try it and watch the field 'Total' being updated dynamically. As it turns out, it even looks much better in datasheet view, because the column is refreshed together with the data fields, while expressions on the form are processed in a second pass (you can see the column being filled after the others).
> What if i do the calculations using a function on a form's event?
Then there is absolutely no way to sort, not even through code. This happens in a third pass, and you also do not have an event called Form_Redraw() or something like that, to perform calculations on the record being displayed rather than the current record.
(°v°)
Business Accounts
Answer for Membership
by: LSMConsultingPosted on 2008-07-19 at 03:44:04ID: 22042122
You can't do this via the standard Access interface buttons, but you can add your own Sort buttons to the form and sort the recordsource by your calculated columns. For example:
Sub SortByCalcColumn()
Me.Recordsource = "SELECT blah blah ORDER BY NameOfMyCalcColumn"
End Sub