Problem Sorting

[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?

 

[Ofer Cohen]

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

 

[bw]

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

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

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?

 

[bw]

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???