Order By using Choose not working

[NevilleT]

I have a query with a choose statement that refers to a form. I use an
option box on the form to decide how to sort the records. The Order By line
is:

ORDER BY Choose(Round([Forms]![frmGanttChart].[opgSortBy],0),
CInt(nz([MSPID],0)),
Format([StartDate],"dd/mm/yyyy"),
Format([FinishDate],"dd/mm/yyyy"));

The two date sorts work fine. The MSPID will not work. It sorts
alphabetically - 1, 10, 11, 12, 2 etc., not numerically.

The field MSPID is an integer in the table. I have tried using Val, Round
and tried to add the nz to cater for nulls. Nothing works. I checked the
option group number was correct and that is fine. It is either 1, 2 or 3.
Running out of ideas.

 

[Allen Browne]

The expression you used in the ORDER BY clause: what do you intend its data
type to be? Are you wanting Access to treat it as a number, or as text?

Text expressions are sorted character by character, so sorting would be:
10, 1001, 101, 11, 12000000, 13, ...
The numeric sorting of the same data would be completely different.

In your expression, you used CInt() which generates numeric data, and the
Format() function which generates text data. Access looks at this, and
decides to use text sorting (since all numbers can be treated as text, but
not all text can be treated as numbers.)

I'm not sure what you want to achieve, but hopefully that will at least help
you understand why you may not be getting the results you expected.

 

[NevilleT]

Hi Allan. Long time no speak. I want to sort the dates in date order and
the MSPID in numeric.

The expression you used in the ORDER BY clause: what do you intend its data
type to be? Are you wanting Access to treat it as a number, or as text?

Text expressions are sorted character by character, so sorting would be:
10, 1001, 101, 11, 12000000, 13, ...
The numeric sorting of the same data would be completely different.

In your expression, you used CInt() which generates numeric data, and the
Format() function which generates text data. Access looks at this, and
decides to use text sorting (since all numbers can be treated as text, but
not all text can be treated as numbers.)

I'm not sure what you want to achieve, but hopefully that will at least help
you understand why you may not be getting the results you expected.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query with a choose statement that refers to a form. I use an
option box on the form to decide how to sort the records. The Order By
line
is:

ORDER BY Choose(Round([Forms]![frmGanttChart].[opgSortBy],0),
CInt(nz([MSPID],0)),
Format([StartDate],"dd/mm/yyyy"),
Format([FinishDate],"dd/mm/yyyy"));

The two date sorts work fine. The MSPID will not work. It sorts
alphabetically - 1, 10, 11, 12, 2 etc., not numerically.

The field MSPID is an integer in the table. I have tried using Val, Round
and tried to add the nz to cater for nulls. Nothing works. I checked the
option group number was correct and that is fine. It is either 1, 2 or 3.
Running out of ideas.

 

[Allen Browne]

You will need to craft an expression that returns something the computer can
sort, either a date value, or a numeric value.

To help you debug this, check the Show box in query design under this
expression. Access will probably add an alias such as Expr1. Then watch the
output in datasheet view. If it left-aligns, Access is treating it as text
and will sort it that way. If it right-aligns, Access is treating is as a
number or date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allan. Long time no speak. I want to sort the dates in date order and
the MSPID in numeric.

The expression you used in the ORDER BY clause: what do you intend its
data
type to be? Are you wanting Access to treat it as a number, or as text?

Text expressions are sorted character by character, so sorting would be:
10, 1001, 101, 11, 12000000, 13, ...
The numeric sorting of the same data would be completely different.

In your expression, you used CInt() which generates numeric data, and the
Format() function which generates text data. Access looks at this, and
decides to use text sorting (since all numbers can be treated as text,
but
not all text can be treated as numbers.)

I'm not sure what you want to achieve, but hopefully that will at least
help
you understand why you may not be getting the results you expected.

I have a query with a choose statement that refers to a form. I use an
option box on the form to decide how to sort the records. The Order By
line
is:

ORDER BY Choose(Round([Forms]![frmGanttChart].[opgSortBy],0),
CInt(nz([MSPID],0)),
Format([StartDate],"dd/mm/yyyy"),
Format([FinishDate],"dd/mm/yyyy"));

The two date sorts work fine. The MSPID will not work. It sorts
alphabetically - 1, 10, 11, 12, 2 etc., not numerically.

The field MSPID is an integer in the table. I have tried using Val,
Round
and tried to add the nz to cater for nulls. Nothing works. I checked
the
option group number was correct and that is fine. It is either 1, 2 or
3.
Running out of ideas.

 

[NevilleT]

OK Allen. That is helpful. Dates are OK but MSPID is left aligned. Need to
work on that.

You will need to craft an expression that returns something the computer can
sort, either a date value, or a numeric value.

To help you debug this, check the Show box in query design under this
expression. Access will probably add an alias such as Expr1. Then watch the
output in datasheet view. If it left-aligns, Access is treating it as text
and will sort it that way. If it right-aligns, Access is treating is as a
number or date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allan. Long time no speak. I want to sort the dates in date order and
the MSPID in numeric.

The expression you used in the ORDER BY clause: what do you intend its
data
type to be? Are you wanting Access to treat it as a number, or as text?

Text expressions are sorted character by character, so sorting would be:
10, 1001, 101, 11, 12000000, 13, ...
The numeric sorting of the same data would be completely different.

In your expression, you used CInt() which generates numeric data, and the
Format() function which generates text data. Access looks at this, and
decides to use text sorting (since all numbers can be treated as text,
but
not all text can be treated as numbers.)

I'm not sure what you want to achieve, but hopefully that will at least
help
you understand why you may not be getting the results you expected.

I have a query with a choose statement that refers to a form. I use an
option box on the form to decide how to sort the records. The Order By
line
is:

ORDER BY Choose(Round([Forms]![frmGanttChart].[opgSortBy],0),
CInt(nz([MSPID],0)),
Format([StartDate],"dd/mm/yyyy"),
Format([FinishDate],"dd/mm/yyyy"));

The two date sorts work fine. The MSPID will not work. It sorts
alphabetically - 1, 10, 11, 12, 2 etc., not numerically.

The field MSPID is an integer in the table. I have tried using Val,
Round
and tried to add the nz to cater for nulls. Nothing works. I checked
the
option group number was correct and that is fine. It is either 1, 2 or
3.
Running out of ideas.

 

[NevilleT]

Actually I am wrong. All is left aligned.

You will need to craft an expression that returns something the computer can
sort, either a date value, or a numeric value.

To help you debug this, check the Show box in query design under this
expression. Access will probably add an alias such as Expr1. Then watch the
output in datasheet view. If it left-aligns, Access is treating it as text
and will sort it that way. If it right-aligns, Access is treating is as a
number or date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allan. Long time no speak. I want to sort the dates in date order and
the MSPID in numeric.

The expression you used in the ORDER BY clause: what do you intend its
data
type to be? Are you wanting Access to treat it as a number, or as text?

Text expressions are sorted character by character, so sorting would be:
10, 1001, 101, 11, 12000000, 13, ...
The numeric sorting of the same data would be completely different.

In your expression, you used CInt() which generates numeric data, and the
Format() function which generates text data. Access looks at this, and
decides to use text sorting (since all numbers can be treated as text,
but
not all text can be treated as numbers.)

I'm not sure what you want to achieve, but hopefully that will at least
help
you understand why you may not be getting the results you expected.

I have a query with a choose statement that refers to a form. I use an
option box on the form to decide how to sort the records. The Order By
line
is:

ORDER BY Choose(Round([Forms]![frmGanttChart].[opgSortBy],0),
CInt(nz([MSPID],0)),
Format([StartDate],"dd/mm/yyyy"),
Format([FinishDate],"dd/mm/yyyy"));

The two date sorts work fine. The MSPID will not work. It sorts
alphabetically - 1, 10, 11, 12, 2 etc., not numerically.

The field MSPID is an integer in the table. I have tried using Val,
Round
and tried to add the nz to cater for nulls. Nothing works. I checked
the
option group number was correct and that is fine. It is either 1, 2 or
3.
Running out of ideas.

 

[NevilleT]

Once again your wisdom has triumphed. The "Yoda" of Microsoft Access. This
is how I got it to work master. Simply put a Val around it.

Expr1: Val(Choose(Round([Forms]![frmGanttChart].[opgSortBy],0),
Val([MSPID]),
Format([StartDate],"yyyymmdd"),
Format([FinishDate],"yyyymmdd")))

Many thanks again Allen.

Actually I am wrong. All is left aligned.

You will need to craft an expression that returns something the computer can
sort, either a date value, or a numeric value.

To help you debug this, check the Show box in query design under this
expression. Access will probably add an alias such as Expr1. Then watch the
output in datasheet view. If it left-aligns, Access is treating it as text
and will sort it that way. If it right-aligns, Access is treating is as a
number or date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allan. Long time no speak. I want to sort the dates in date order and
the MSPID in numeric.

:

The expression you used in the ORDER BY clause: what do you intend its
data
type to be? Are you wanting Access to treat it as a number, or as text?

Text expressions are sorted character by character, so sorting would be:
10, 1001, 101, 11, 12000000, 13, ...
The numeric sorting of the same data would be completely different.

In your expression, you used CInt() which generates numeric data, and the
Format() function which generates text data. Access looks at this, and
decides to use text sorting (since all numbers can be treated as text,
but
not all text can be treated as numbers.)

I'm not sure what you want to achieve, but hopefully that will at least
help
you understand why you may not be getting the results you expected.

I have a query with a choose statement that refers to a form. I use an
option box on the form to decide how to sort the records. The Order By
line
is:

ORDER BY Choose(Round([Forms]![frmGanttChart].[opgSortBy],0),
CInt(nz([MSPID],0)),
Format([StartDate],"dd/mm/yyyy"),
Format([FinishDate],"dd/mm/yyyy"));

The two date sorts work fine. The MSPID will not work. It sorts
alphabetically - 1, 10, 11, 12, 2 etc., not numerically.

The field MSPID is an integer in the table. I have tried using Val,
Round
and tried to add the nz to cater for nulls. Nothing works. I checked
the
option group number was correct and that is fine. It is either 1, 2 or
3.
Running out of ideas.

 

[Stefan Hoffmann]

hi Neville,

OK Allen. That is helpful. Dates are OK but MSPID is left aligned.

As Allen wrote, the statement is evaluated that the ORDER BY uses
Strings. So change your Integer to a sortable string:

(Sgn(CInt(Nz([MSPID], 0))) + 1) &
Right("000000" & Abs(CInt(Nz([MSPID], 0))), 6)

The amount of 0 padded on the left side depend on the data type (as many
0's as the largest value has digits):


mfG
--> stefan <--