Sort Order

[Guest]

I have a report that is treating a long int (autonumber field) as a string
when sorted (e.g. 1,10,100,11,110, etc.). I want it to sort as a number.

I have other reports which sort the field correctly but have been unable to
determine the difference between them.

The sort order is being dynamically decided by a form which loads the
report. Therefore I can sort by name, acct#, address, etc. I don't see how
this is affecting the number being treated as a string though.

Any ideas?
thanks,
James

 

[Guest]

Well, I just figured out how to get it to sort correctly, but I don't
understand why.
inside my grouping levels I have statements such as
=IIf(Forms![PB dg]!optSort=5,[ZIP],[Acct#])
to sort based on a selection from a form which loaded the report. This
causes the report to sort as a string, but if I seperate the [Acct#] check as
a group by itself it sorts as a number.

I tried placing the [Acct#] check at the beginning of the line in the
grouping level and it makes no difference. It is still sorted as a string. I
tried casting to a long int
=IIf(Forms![PB dg]!optSort=5,[ZIP],Clng([Acct#])) and it still sorts as a
string.

Do you know somewhere this is placed as a limitation, or some way to
overcome this limitation? I haven't run into this in any of my reading.
Thanks,
James

 

[fredg]

I have a report that is treating a long int (autonumber field) as a string
when sorted (e.g. 1,10,100,11,110, etc.). I want it to sort as a number.

I have other reports which sort the field correctly but have been unable to
determine the difference between them.

The sort order is being dynamically decided by a form which loads the
report. Therefore I can sort by name, acct#, address, etc. I don't see how
this is affecting the number being treated as a string though.

Any ideas?
thanks,
James

Any sort order in your form or query is irrelevant to the sort order
of a report.
Sort the report using the Report's Sorting and Grouping dialog.
View + Sorting and Grouping

 

[John Spencer]

The problem is that Access must decide on ONE type of value to be returned by
the IIF. If you give it the option to return a number or a text string, it goes
with the most universal. Since ZIP is a text field it decides on text as the
type - text can handle numbers; number fields cannot handle all text strings.

You might be able to solve this particular problem, IF your zip codes don't
include any that start with zero. You can use Val to return a value (number) or
convert the string using CLng(). Of course, that could error if you have nulls
in the field. Although you could handle that with CLng(NZ([ZIP],0))



=IIf(Forms![PB dg]!optSort=5,Val([ZIP]),[Acct#])

Another method that might work would be to format [Acct#] so it has lots of
leading zeroes. You only need to apply the format in the Sorting and grouping.

=IIf(Forms![PB dg]!optSort=5,[ZIP], Format([Acct#],"00000000000000"))

Well, I just figured out how to get it to sort correctly, but I don't
understand why.
inside my grouping levels I have statements such as
=IIf(Forms![PB dg]!optSort=5,[ZIP],[Acct#])
to sort based on a selection from a form which loaded the report. This
causes the report to sort as a string, but if I seperate the [Acct#] check as
a group by itself it sorts as a number.

I tried placing the [Acct#] check at the beginning of the line in the
grouping level and it makes no difference. It is still sorted as a string. I
tried casting to a long int
=IIf(Forms![PB dg]!optSort=5,[ZIP],Clng([Acct#])) and it still sorts as a
string.

Do you know somewhere this is placed as a limitation, or some way to
overcome this limitation? I haven't run into this in any of my reading.
Thanks,
James

I have a report that is treating a long int (autonumber field) as a string
when sorted (e.g. 1,10,100,11,110, etc.). I want it to sort as a number.

I have other reports which sort the field correctly but have been unable to
determine the difference between them.

The sort order is being dynamically decided by a form which loads the
report. Therefore I can sort by name, acct#, address, etc. I don't see how
this is affecting the number being treated as a string though.

Any ideas?
thanks,
James