Problem Sorting

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

If I put [DBNum]*1000+[Inst Num] in the Sorting and Grouping box, it
sorts correctly.

The value of: [Forms]![boxSeats]![SortBy] can be 1 or 2.
I want to sort by [BankName] if 1.
I want to sort by [DBNum]*1000+[Inst Num] if 2.

I am putting the following in the Sorting and Grouping box, (this
doesn't sort properly):

=IIf([Forms]![boxSeats]![SortBy]=1,[BankName],[DBNum]*1000+[Inst Num])

When "SortBy" is 1, it sorts okay. When "SortBy" is 2, it DOES NOT
sort properly.

Can someone suggest what I'm doing wrong?
 
In the RecordSource of the Report add a field

Select TableName.* , IIf ([Forms]![boxSeats]![SortBy] = 1,
[BankName],[DBNum]*1000+[Inst Num]) As NewFieldName From TableName

In the Grouping and sorting you can sort by the new field NewFieldName
 
Thanks for the reply.

I am getting a syntax error doing what you said. Can you "spell this
out" by telling me the code to put on each line of the Record Source?
What goes on the FIELD row, the TABLE row,

I am currently using the following...which does not work.
FIELD: SrtOrder:
=IIf([Forms]![boxSeats]![SortBy]=1,[BankName],[DBNum]*1000+[Inst Num])
I then sort on SrtOrder.

On the report, I have put some controls to show the values of
[Forms]![boxSeats]![SortBy] and SrtOrder.
The Value of SrtOrder is correct when [Forms]![boxSeats]![SortBy]=1.
The value of SrtOrder is NOT correct when
[Forms]![boxSeats]![SortBy]=2...(I don't know if or on what it is
sorting here)

Bernie

Ofer Cohen said:
In the RecordSource of the Report add a field

Select TableName.* , IIf ([Forms]![boxSeats]![SortBy] = 1,
[BankName],[DBNum]*1000+[Inst Num]) As NewFieldName From TableName

In the Grouping and sorting you can sort by the new field NewFieldName

--
Good Luck
BS"D


bw said:
If I put [DBNum]*1000+[Inst Num] in the Sorting and Grouping box, it
sorts correctly.

The value of: [Forms]![boxSeats]![SortBy] can be 1 or 2.
I want to sort by [BankName] if 1.
I want to sort by [DBNum]*1000+[Inst Num] if 2.

I am putting the following in the Sorting and Grouping box, (this
doesn't sort properly):

=IIf([Forms]![boxSeats]![SortBy]=1,[BankName],[DBNum]*1000+[Inst
Num])

When "SortBy" is 1, it sorts okay. When "SortBy" is 2, it DOES NOT
sort properly.

Can someone suggest what I'm doing wrong?
Click to expand...
Click to expand...
 
I have discovered the problem, although I don't know why this is
happening, or how to fix it.

If I simply reverse the order of the Field from this...
SortBy: =IIf([Forms]![boxSeats]![SortBy]=1,[BankName],[DBNum]*1000+[Inst
Num])

to this...
SortBy: =IIf([Forms]![boxSeats]![SortBy]=1,[DBNum]*1000+[Inst
Num],[BankName])

IT WORKS!

Can someone explain this???
 
Back
Top