Sorting currency columns

And the user is required to enter a value in either column but not both.

So when they enter a value in either of the cells the other cell gets set to null (blanked).

The problem we had was with sorting the currency.

If I entered 10.00 on the first row, 200.00 on the second and 30.00 on the third the sorting would put the 200.00 in the middle.

So looking at examples on the forum, I changed the item.value to be double and not string. This resolves the sorting issue but I now cannot have a null cell on that column. Further examples in the forum state I can use the item.caption but this seems not the best way to do it as the item.value may get changed in code and the new value is not reflected on the grid. It also causes problems with formatting.

The attached examples are:

CodeJockReportV1.zip - shows the way it originally worked setting a cell to blank when the other is entered.

Enter data as :

A <tab> 10 <cr>

B <tab> 200 <cr>

C <tab> 30 <cr>

Sort the Fixed Price column by clicking the column header.

Notice that the order is wrong.

Also notice how we want it to work, ie. enter a value in the Fixed Price column, then move to the Discount column and enter a value in there. It clears out the Fixed Price cell. And vice versa.

So, the second example attached (CodeJockReportV2.zip) was an attempt to resolve the issue.

The only differences are :

1. AddRecordPart adds the FixedPrice as double and not a string

2. ClearHeaderRow defaults the FixedPrice to zero from null.

3. _BeforeDrawRow formats the value

But I cannot get it to null the other column's cell data and format the number.

Sometimes when entering 8.87 it rounds up to 9.00 but you may not experience this once written correctly.uploads/6827/CodeJockReportV2.zip

 

Easiest way, to prevent showing '0'  is to use Metrics.Text in BeforeDrawRow event

 

' ** CODE ADDED HERE
    If Item.Index = clmPriceFixed Then
        If Item.Value <> "" Then
            If Item.Value <> 0 Then
                Item.Caption = Format(Trim(Item.Value), "##0.#0")
            Else
                Metrics.Text = ""
            End If
        End If
    End If
    ' ** CODE END

 

 

A mixture of both answers has sorted the problem.

Ultimately it seemed that the setting of the Item.Value in the _ValueChanged event causes the sorting to go wrong.

Thanks again for the very fast response.

Cheers, Col