Sorting Numbers in a Text Field

[GGoodell]

Is there any way to sort numbers in a text field in true numerical order? For
example if I enter the numbers 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and 21
in a text field and then sort they come out in the order 1, 10,11, 12, 2, 21,
3, 4, 5, 6, 7, 8 and 9. I know this is text sorting. I need to maintain the
field as a text field but would like to sort numbers the way they normally do.

Any advice is appreciated!

Greg Goodell

 

[GGoodell]

Does the "val[FieldNam] go in the query or can I only put it in the report
detail section?

 

[Rick Brandt]

Is there any way to sort numbers in a text field in true numerical
order? For example if I enter the numbers 1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12 and 21 in a text field and then sort they come out in the
order 1, 10,11, 12, 2, 21, 3, 4, 5, 6, 7, 8 and 9. I know this is
text sorting. I need to maintain the field as a text field but would
like to sort numbers the way they normally do.

Any advice is appreciated!

Greg Goodell

Sort on Val(FieldName).

 

[Arvin Meyer [MVP]]

In addition to Rick's suggestion, you can add a column to your query with
the expression:

Format([MyFieldName], "0000")

Add more places if you need them. Sort on this column.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[GGoodell]

I ended up using the VAL function in the Query and it works great.

Arvin- if I use the "0000" format will it print the preceding zeros? I don't
want it to but that may help with a another problem I have.

Thanks for help!

Greg

In addition to Rick's suggestion, you can add a column to your query with
the expression:

Format([MyFieldName], "0000")

Add more places if you need them. Sort on this column.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Is there any way to sort numbers in a text field in true numerical order? For
example if I enter the numbers 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and 21
in a text field and then sort they come out in the order 1, 10,11, 12, 2, 21,
3, 4, 5, 6, 7, 8 and 9. I know this is text sorting. I need to maintain the
field as a text field but would like to sort numbers the way they normally do.

Any advice is appreciated!

Greg Goodell

 

[Rick Brandt]

I ended up using the VAL function in the Query and it works great.

Arvin- if I use the "0000" format will it print the preceding zeros?
I don't want it to but that may help with a another problem I have.

My problem with the Format() suggestion is that Format() is actually
intended to display numbers and dates in a specific text format. When you
feed text data in as the input it is actually coerced into a number first
and then formatted back to text. I see no reason to convert the data twice
in this case.

 

[GGoodell]

I just noticed on my reports that any value that had text in it is now
showing up as zero. Shouldn't the Text being showing up? What did I do wrong?

Thanks

Greg

I ended up using the VAL function in the Query and it works great.

Arvin- if I use the "0000" format will it print the preceding zeros? I don't
want it to but that may help with a another problem I have.

Thanks for help!

Greg

In addition to Rick's suggestion, you can add a column to your query with
the expression:

Format([MyFieldName], "0000")

Add more places if you need them. Sort on this column.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Is there any way to sort numbers in a text field in true numerical order? For
example if I enter the numbers 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and 21
in a text field and then sort they come out in the order 1, 10,11, 12, 2, 21,
3, 4, 5, 6, 7, 8 and 9. I know this is text sorting. I need to maintain the
field as a text field but would like to sort numbers the way they normally do.

Any advice is appreciated!

Greg Goodell

 

[Arvin Meyer [MVP]]

I ended up using the VAL function in the Query and it works great.

Arvin- if I use the "0000" format will it print the preceding zeros? I don't
want it to but that may help with a another problem I have.

Yes, it will display like: 0123

if you have the show box in the query column checked (it is by default).
Turn the show column box off if all you want to do is sort.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[CCross]

Where exactly would you put =Val(fieldname) in a query or can you designate
this sort order in a table?

Does the "val[FieldNam] go in the query or can I only put it in the
report detail section?

If your goal is only top sort your report you cn simply open the sorting and
grouping dialog and enter...

=Val(YourFieldName)

....into the box labelled "Field/Expression".

If you think you might want to do "other" numeric operations on the value then
it would be better to add it to the query.

 

[Rick Brandt]

Where exactly would you put =Val(fieldname) in a query or can you
designate this sort order in a table?

Add Val(FieldName) as a calculated field in the output of the query. Then
put the sort on that column.

 

[CCross]

I tried the Val function and now the numbers are all over the place. When I
look at the Val field, every one is 1.1. I don't know if this matters, but I
imported this information from excel. The numbers that I want to sort or
like this:
1.10.5
1.10.5.1
1.10.5.1.a
1.10.5.2
1.10.5.10

Thanks, Carol

 

[John Vinson]

I tried the Val function and now the numbers are all over the place. When I
look at the Val field, every one is 1.1. I don't know if this matters, but I
imported this information from excel. The numbers that I want to sort or
like this:
1.10.5
1.10.5.1
1.10.5.1.a
1.10.5.2
1.10.5.10

The Val() function returns the numeric value of the first substring of
the field which can be interpreted as a number. In all of these cases
that number is in fact 1.10, since numbers cannot have two decimal
places.

You're not sorting by a number. The text strings you show here are not
numbers, though they contain numeric portions. What is the sum of
1.10.5.1.a and 1.10.5? what is the product of these two "numbers"?

You're sorting by up to FOUR numbers, plus a text value.

A better design for this composite, multivalued field would be to have
*five fields*, four integers and a Text (based on the example you
presented). They can be concatenated for display purposes if you wish.

John W. Vinson[MVP]