Choose function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a generated field in a query which is used to sort the recordset based on the selection of a sort method by the user of the database (the user can make a selection from a list box on a form)

i.e. the list box has two columns, first one is bound and has values 1, 2 3 etc. The second column has the various methods of sorting i.e. "By Date","By Price" etc

The list box is named lstSortBy. The underlying table I'm querying contains two fields, [Date Details Received] and [Quoting Price], that I want to sort by depending on the users selection from the list box

I then have a field in the query that is generated by the following expression

SortBy: Choose([forms]![frmName]![lstSortBy],[Date Details Received],[Quoting Price]). The Sort Order on the field is then set to Descending

However, it seems that whilst the right field is being chosen, the Sorting is being applied as if the two fields where text fields rather than as date and currency datatypes (which the two fields are respectively in the underlying table

i.e. if the various quoting prices where say

9000000
9
9800

The result set using the Choose function above is coming out as

9
9800
90000000

rather than a

9000000
9800
9

as is the intention

Can anybody help? I did make the following amendment to the Choose function

Choose([forms]![frmName]![lstSortBy],CLng([Date Details Received]),[Quoting Price])

in an effort to convert the Date field to a number that the sort function would work on and which did work with the [Date Details Received] field but when I tried to use the CLng or CInt functions on the [Quoting Price] field, I kept getting an error message saying the Query was too complex

Any ideas
 
Try:
SortBy: Choose([forms]![frmName]![lstSortBy],Format([Date Details Received],
"yyyymmdd"),Format([Quoting Price],"000000000000.0000")

--
Duane Hookom
Microsoft Access MVP


Andrew said:
I have a generated field in a query which is used to sort the recordset
Click to expand...
based on the selection of a sort method by the user of the database (the
user can make a selection from a list box on a form).
i.e. the list box has two columns, first one is bound and has values 1, 2
Click to expand...
3 etc. The second column has the various methods of sorting i.e. "By
Date","By Price" etc.
The list box is named lstSortBy. The underlying table I'm querying
Click to expand...
contains two fields, [Date Details Received] and [Quoting Price], that I
want to sort by depending on the users selection from the list box.
I then have a field in the query that is generated by the following expression:

SortBy: Choose([forms]![frmName]![lstSortBy],[Date Details
Click to expand...
Received],[Quoting Price]). The Sort Order on the field is then set to
Descending.
However, it seems that whilst the right field is being chosen, the Sorting
Click to expand...
is being applied as if the two fields where text fields rather than as date
and currency datatypes (which the two fields are respectively in the
underlying table)
i.e. if the various quoting prices where say:

90000000
99
98000

The result set using the Choose function above is coming out as:

99
98000
90000000

rather than as

90000000
98000
99

as is the intention.

Can anybody help? I did make the following amendment to the Choose function:

Choose([forms]![frmName]![lstSortBy],CLng([Date Details Received]),[Quoting Price])

in an effort to convert the Date field to a number that the sort function
Click to expand...
would work on and which did work with the [Date Details Received] field but
when I tried to use the CLng or CInt functions on the [Quoting Price] field,
I kept getting an error message saying the Query was too complex.
 
Back
Top