Format and Sort

[FrankMars]

I formatted a number field in a query in order to have it display hard right w/o spaces (Format([SF],"##,##0"). My problem is it will no longer sort correctly in datasheet view. Thanks in advance.

 

[r937]

keep both the formatted and unformatted number in the query, and sort on the unformatted number

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[FrankMars]

Thanks r but I'd rather not keep both. Is there another way?

 

[missinglinq]

No! If you're going to sort on a field that you've converted into Text that's how it's going to Sort. What is your probelm with having both Fields in the Query?

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[FrankMars]

Thanks linq. I'm trying to view/analyze a few critical numbers in a continuous form in datasheet view which is based on this query. I'm trying to keep the datasheet as uncluttered as possible (thus not including the extra field for sorting), formatting in a certain way for reading purposes (thus the hard right alignment), and still have the functionality of sorting. Maybe I'm asking for too much as an amateur user.

 

[missinglinq]

Are you talking about having it sorted on this Number when the Datasheet View Form initially opens? If so, do as r937instructed, and simply don't include the 'sorting' Field on your Form! It can be used to sort the Query, and hence the Form, without actually appearing on the Form.

Linq ;0)>

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[FrankMars]

I'd like to be able to sort Asend/Desend by right clicking the column head.

 

[dhookom]

You can use an expression like:

Right(Space(10) & Format([SF],"#,##0"),10)

But I much prefer not converting a number to a string.

Have you considered changing the format property rather than changing the expression?

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

I don't think you can use this by using the native Sort Buttons from the Right-Click Menu, but you can by using OrderBy and Double-Clicking the displayed Field but Ordering By the non-displayed Field. In this example, the SortByField is just that, the Field that is not displayed but is to be sorted on, and DisplayedFieldis just that, the Field you actually see on the Form and Double-Click on:

Private Sub DisplayedField_DblClick(Cancel As Integer)
 
 If Me.OrderBy = "SortByField Desc" Then
  Me.OrderBy = "SortByField Asc"
 Else
  Me.OrderBy = "SortByField Desc"
 End If
 
Me.OrderByOn = True

End Sub

Each Double-Click reverses the order from the currently viewed order the opposite order.

Linq ;0)>

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[FrankMars]

I've tried to create a datasheet that groups information into 'chunks' for quicker/easier analysis. I've formatted and concatenated information to the core numbers for easier reading. Judging from the responses, I'm guessing this isn't such a good idea. So what I'll do is lose the formatting and pull the concats out and put them in tween fields, thus keeping the number fields pure for sorting. I'll keep the VBA from Linq and the expression from Hook in my back pocket for down the road. Thanks very much for your help!

 

[FrankMars]

One final question on this issue. Is it possible to "hard right" align a currency or standard field with zero decimal places in a form datasheet column without converting it to a string?

 

[dhookom]

Did you try leave the control source as the number and simply set the Format property to Currency with 0 Decimals?

Duane
Hook'D on Access
MS Access MVP

 

[FrankMars]

Yes I did. It does align to the right but there is still an indent which I am trying to eliminate.

 

[dhookom]

Indent? Left or right? How much of an indent? I doubt there is anything you can do about this in datasheet.

Duane
Hook'D on Access
MS Access MVP

 

[FrankMars]

Looks like maybe 2 decimal places from the right column line.

 

[dhookom]

If the numbers were right-aligned, I would live with it or try other numeric formats.

Duane
Hook'D on Access
MS Access MVP

 

[FrankMars]

Gotcha.