Number Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan H - 23 Aug 2004 23:35 GMT

Every time I enter the number 1.10 in the field it changes
to 1.1

I need the field to be able to hold the following number
pattern

1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 1.10, 1.11,
1.12..etc.

Can anyone tell me why this keeps happening?

Thanks,

Dan H.

Reply to this Message

Ken Snell [MVP] - 24 Aug 2004 00:34 GMT

Because your field is formatted as a number. 1.10 is stored/displayed as
1.1, because ACCESS does not show "significant" number decimal places.

Change the field format to Text in order to store the data the way you want.
Note that a text field will sort in a nonnumeric way, meaning 1.11 will
appear before 1.2 when using ascending order. If you want a numeric sort
behavior, you'll need to use one or more calculated fields (which will
convert the different parts of the text string into appropriate number
values) in the query as the sorting fields.

Signature

Ken Snell
<MS ACCESS MVP>

> Every time I enter the number 1.10 in the field it changes
> to 1.1
[quoted text clipped - 10 lines]
>
> Dan H.

Reply to this Message

Dan H. - 24 Aug 2004 00:49 GMT

Thanks for you reply.

I understand making the field a text field, I'm not sure
if I know how to create the numeric sort behavior by using
one or more calculated fields to convert the text string.

Would you happen to have an example? or maybe know of a
knowledge based article that would explain this procedure?

Thanks again,

Dan H.

>-----Original Message-----
>Because your field is formatted as a number. 1.10 is stored/displayed as
[quoted text clipped - 22 lines]
>
>.

Reply to this Message

Ken Snell [MVP] - 24 Aug 2004 01:51 GMT

With your setup, I'd use the Val function to convert to a number, and I'd
use two fields (this assumes that you'll never use more than two numbers
after the decimal point in your values):

ValueSort1: CInt(Val([FieldName]))
ValueSort2: Val(Mid([FieldName], InStr([FieldName], ".") + 1))

Then my query would look something like this:

SELECT * FROM TableName
ORDER BY CInt(Val([FieldName])),
Val(Mid([FieldName], InStr([FieldName], ".") + 1))

Signature

Ken Snell
<MS ACCESS MVP>

> Thanks for you reply.
>
[quoted text clipped - 45 lines]
> >
> >.

Reply to this Message