Left Align (trim) in report issue

[JohnWA]

Hi everyone,
Basically I have a table that is set up to auto import data from a TXT file
and from a query produce a report with the field (text) sorted in numerical
order. The problem I have is some of the data imports leading spaces as well
as the text. I need to left align the data and exclude the spaces to get the
numerical sort right. I see there is a function LTRIM but that is as far as
I have got - where do I apply what to end up with the desired result - any
help would be greatly appreciated

Regards Bryan

 

[Marshall Barton]

Basically I have a table that is set up to auto import data from a TXT file
and from a query produce a report with the field (text) sorted in numerical
order. The problem I have is some of the data imports leading spaces as well
as the text. I need to left align the data and exclude the spaces to get the
numerical sort right. I see there is a function LTRIM but that is as far as
I have got - where do I apply what to end up with the desired result - any
help would be greatly appreciated

If you just use the LTrim function, the result will still be
a text string and will sort in dictionary order (e.g.
1,10,100,2,20,3,300)

If you have numbers in a text field and you want to sort
numerically, then set the Sorting and Grouping to use:
=Val(thefield)
instead of just the field name.

Val will ignore the leading spaces and convert the digit
characters to a number so it sorts numerically (e.g.
1,2,3,10,20,100,300)