text 123.456 into sortable numbers

[Guest]

Hi, another nb question:
How can I convert text, formed 123.456 into number format so, that it is
sorted primarily according to the numbers on the left and then on the right
side? I am using comma as a decimal point, so I cannot convert the index
number into decimal.

Any help appreciated!
Thanks, Jaakko

 

[Van T. Dinh]

You can use CLng, Left, Mid and InStr to obtain the 2 numeric values as
follows:

?CLng(Left("123.456", InStr("123.456",".") - 1))
123
?CLng(Mid("123.456", InStr("123.456",".") + 1))
456

OTOH, you are storing 2 different items of data in one Field value which
violates the First Normal Form of the Relational Database Design Theory.
Perhaps, you should consider splitting this into 2 separate Fields.

 

[Van T. Dinh]

I tend to show / display what the users want to see but underneath, I stick
to Relational Database Design Theory. The users don't need to see how I
actually compartmentize the data ... and if they see what they want to see,
that should keep them happy (to pay my invoices!).