Number Field

[Dan H]

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.

 

[Ken Snell [MVP]]

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.

 

[Dan H.]

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.

 

[Ken Snell [MVP]]

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