1, 11, 12 instead of 1, 2, 3...

  • Thread starter Thread starter backBack
  • Start date Start date
B

backBack

In a query (thus in a form) I have the following results
for a specificic field set by alphabatical order: 1, 11,
12 [...] 2, 21, 22. What I wish to see is: 1, 2, 3 [...]
11, 12 etc. I suspect that there is something I should do
at the data entry level (or in settings?) to address the
problem. Of course, at date entrey level, if I enter 01,
02 03, I will have 01, 02, 03 as a result of the
query/form, but it's not what I want.

Thank you for helping

bacBack
 
backBack said:
In a query (thus in a form) I have the following results
for a specificic field set by alphabatical order: 1, 11,
12 [...] 2, 21, 22. What I wish to see is: 1, 2, 3 [...]
11, 12 etc. I suspect that there is something I should do
at the data entry level (or in settings?) to address the
problem. Of course, at date entrey level, if I enter 01,
02 03, I will have 01, 02, 03 as a result of the
query/form, but it's not what I want.
Click to expand...

What you are seeing is the result of storing and sorting a text field
instead of a number field.

Is there a reason you didn't use a number field? If there is a reason to
store as text you can convert to a number in queries and reports to get a
numerical sort instead of a text sort. There are a number of functions for
this but IU usually use...

=Val([YourField)
 
What you are seeing is the difference between the way Access (and most other
programs) evaluates and sorts Text Fields and Number Fields. Your fields
are Text Fields (or are being evaluated as such) and in order to sort them,
Access evaluates each character as opposed to evaluating the value of a
Number Field.

Your possible options:

1. If your fields will never, ever have non-numeric characters entered,
change the Data Type to Number
2. In your Query, force your fields to be evaluated as numbers by using the
Val() function; for example:

SortField: Val([MyTextField])

This will work only if the fields in question begin with numeric
characters or are all numeric characters.

hth,
 
-----Original Message-----
In a query (thus in a form) I have the following results
for a specificic field set by alphabatical order: 1, 11,
12 [...] 2, 21, 22. What I wish to see is: 1, 2, 3 [...]
11, 12 etc. I suspect that there is something I should do
at the data entry level (or in settings?) to address the
problem. Of course, at date entrey level, if I enter 01,
02 03, I will have 01, 02, 03 as a result of the
query/form, but it's not what I want.
Click to expand...

What you are seeing is the result of storing and sorting a text field
instead of a number field.

Is there a reason you didn't use a number field? If there is a reason to
store as text you can convert to a number in queries and reports to get a
numerical sort instead of a text sort. There are a number of functions for
this but IU usually use...

=Val([YourField)
Click to expand...
Thanks for those who replied.

Comment: Yes there is a reason why I do not use a number
field instead of a text field. In addition, the number
entered in the field is not the first caracter of the
field data. In fact, here's the case. In a database that
stores titlee of classical works, I have this case (for
example):

- Nocturne no 1
- Nocturne no 2
- etc..
- Nocturne no 11
- etc..

and the result of the query is (as you now expect)

- Nocturne no 1
- Nocturne no 11
- etc.
- Nocturne no. 2
- Nocturne no 21, etc.

Am I doomed to live with that? If now, pleas provide me
with info.

With thanks

backBack
 
backBack said:
Thanks for those who replied.

Comment: Yes there is a reason why I do not use a number
field instead of a text field. In addition, the number
entered in the field is not the first caracter of the
field data. In fact, here's the case. In a database that
stores titlee of classical works, I have this case (for
example):

- Nocturne no 1
- Nocturne no 2
- etc..
- Nocturne no 11
- etc..

and the result of the query is (as you now expect)

- Nocturne no 1
- Nocturne no 11
- etc.
- Nocturne no. 2
- Nocturne no 21, etc.

Am I doomed to live with that? If now, pleas provide me
with info.
Click to expand...

You would either need to write an expression that could extract the
numerical portion that you want to sort on out as a separate value or
always pad the numbers with the appropriate number of zeros to get
numeric-type sorting.

Otherwise you're stuck with alpha-sorting.
 
You might also consider splitting the field into two, the description, eg
Nocturne, and the series number. Its a simple matter to combine the two for
display on a form or report, and gives you the flexibility of sorting by the
numerics.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
backBack said:
-----Original Message-----
In a query (thus in a form) I have the following results
for a specificic field set by alphabatical order: 1, 11,
12 [...] 2, 21, 22. What I wish to see is: 1, 2, 3 [...]
11, 12 etc. I suspect that there is something I should do
at the data entry level (or in settings?) to address the
problem. Of course, at date entrey level, if I enter 01,
02 03, I will have 01, 02, 03 as a result of the
query/form, but it's not what I want.
Click to expand...

What you are seeing is the result of storing and sorting a text field
instead of a number field.

Is there a reason you didn't use a number field? If there is a reason to
store as text you can convert to a number in queries and reports to get a
numerical sort instead of a text sort. There are a number of functions for
this but IU usually use...

=Val([YourField)
Click to expand...
Thanks for those who replied.

Comment: Yes there is a reason why I do not use a number
field instead of a text field. In addition, the number
entered in the field is not the first caracter of the
field data. In fact, here's the case. In a database that
stores titlee of classical works, I have this case (for
example):

- Nocturne no 1
- Nocturne no 2
- etc..
- Nocturne no 11
- etc..

and the result of the query is (as you now expect)

- Nocturne no 1
- Nocturne no 11
- etc.
- Nocturne no. 2
- Nocturne no 21, etc.

Am I doomed to live with that? If now, pleas provide me
with info.

With thanks

backBack
Click to expand...
 
Back
Top