Unable to sort a calculated field in a datasheet form

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

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/office/community/en-us/default.mspx?&lang=&cr=&guid=&sloc=en-us&dg=microsoft.public.access.queries&p=1&tid=AD110AE0-5F32-40D2-A7CF-45A64AF784FF&mid=AD110AE0-5F32-40D2-A7CF-45A64AF784FF

LSM can you please explain a little bit more the SELECT syntax?

A work around might be to change the query in an append query and store the result in a temp table.
Trigger a delete * from tblTemp and the append query in the main form's OnOpen and by using the datasheet for the subform, your standard sort will work.

Nic;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°)

I don't want to base the form on a query because the user will not be able to update the data(unless there is something we can do about that also)

What if i do the calculations using a function on a form's event?

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)