sort is disabled in calculated column

  • Thread starter Thread starter Lorenz Ingold
  • Start date Start date
L

Lorenz Ingold

We have an Access 2003 project. On a form, we have some textboxes with some
kind of calculated values, namely (e.g.) "=Branche.Column(1)", where Branche
is the name of a combobox. We have our reasons for doing this; it is that we
must not have the Record source query too complicated because this gives new
problems. So we have a bit a strange workaround with invisible comboboxes
which, in "consuming" only some code columns of the main query, can keep the
main query (record source) more simple. Now we could right click on a column
(on the form) and select "sort ascending" or "sort descending", however,
this option is disabled for such calculated columns (or any calculated
column, I guess). How could this be solved?
 
Hi,



Include the calculated column in a SELECT clause? That means the
recordsource of your form is a query, or an SQL statement, rather than the
table itself.



Hoping it may help,
Vanderghast, Access MVP
 
I don't know exactly understand what you mean that I should do. Was my
question hard to understand?
 
Hi,


Include the computed expression in an SQL statement:


SELECT f1, f2, f3, ... , f1+f2 As computed, ... FROM ...

Use that saved SQL statement as record source of your form.

You should then be able to order by on the computed expression, since it
would appear as an (not updateable) field supplied from the SQL statement.



Hoping it may help,
Vanderghast, Access MVP
 
I tried this, but it didn't work. My original expression was
"=Branche.Column(1)". I tried with this, and with "Branche.Column(1)", and
also with "Forms!Policen!Branche.Column(1)". (Of course always without the
"). The latter one should be the most correct in my opinion, otherwise
Access would not find the data. But none of the above expressions work; as
soon as I want to choose the Control Source of the text control within its
property page, there comes an error message (e.g.) "Undefined function
'Branche.Column' in expression."
 
Hi,



I haven't tried other alternatives, but

=myFunction()


seems to work fine, with:
--------------------------------
Private Function myFunction() As Variant
myFunction = Me.Combo2.Column(1)
End Function
---------------------------------


Note that technically, we need a Variant, so it can handle a NULL value.



Hoping it may help,
Vanderghast, Access MVP
 
With a function in the Code-behind-form module it didn't work (I tried
several variations). I then tried the following:

Public Function BrancheText() As Variant
BrancheText = "test"
End Function

(I placed this function into another module). In the record source of the
form, I included (in the SELECT-clause) the expression 'BrancheText() AS
BrancheName'. And in the text control I take the row source BrancheName.
This works perfectly and the sort-menu-entries in the context menu are
enabled! So I went ahead and tried this time with the correct data:

Public Function BrancheText() As Variant
BrancheText = Forms!Policen!Branche.Column(1)
End Function

However, this doesn't work. When I debug the function, I see that the
expression Forms!Policen!Branche.Column(1) returns NULL. It seems as if the
data are not ready, which can somehow be understood, because the record
source happens to calculate some values before other values, I mean it could
calculate the above text control before the combobox control.

If however, I leave the record source unchanged (without the additional
field), and put '=BrancheText()' into the text control as its row source,
then the correct data appear, but the sort functionality is again disabled.
It seems somewhat hopeless!
 
Back
Top