Left Align (trim) in report issue

  • Thread starter Thread starter JohnWA
  • Start date Start date
J

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
 
JohnWA said:
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
Click to expand...


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)
 
Back
Top