Convert text to a number

[Ron]

I'm trying to convert text values into numbers.

When a new account comes in, it automatically is assigned
a number based on,

year&month&incremented-6-digit-number

To give resulting codes such as,
200310100006
200310100007
200310100008

Ordinarily I'd be satisfied to have this code remain a
text field since I'm not doing math on it, however, as a
text field it sorts differently. And I don't want to
have to bring in all the sub-fields which make up the
code to sort it numerically.

So how can I convert the text 200310100006 as a number?

Thanks in advance, Ron.

[John Vinson]

On Thu, 30 Oct 2003 07:37:58 -0800, "Ron"
<anon...@discussions.microsoft.com> wrote:

>Ordinarily I'd be satisfied to have this code remain a
>text field since I'm not doing math on it, however, as a
>text field it sorts differently.

Example please? If it's a fixed-length text string consisting of all
digits, it will sort identically whether it's Text or Number.

>And I don't want to
>have to bring in all the sub-fields which make up the
>code to sort it numerically.

In a properly designed table the field WOULD NOT EXIST. This is called
an "intelligent key" - not a compliment! Better would be to store the
fields separately (as a joint three-field primary key, if you wish),
and concatenate them for display purposes; there is no benefit to
storing this field period, either as text or a number.

>So how can I convert the text 200310100006 as a number?

No, because it's too big. The largest Long Integer is 2147483647. You
might be able to store it as Currency using a CCur() function call, I
think that gets into the trillions. But as noted... the field ideally
would not exist.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

[Dale Fye]

Personnally, I'd break it up into 3 fields (AcctYear, AcctMonth,
YrMonOrdinal) or something like that.

That 12 character string is too large to convert it to a long integer,
but you it appears to convert to a double precision number OK

?cdbl("200310100006") in the Immediate window returns 200310100006.

--
HTH

Dale Fye

"Ron" <anon...@discussions.microsoft.com> wrote in message
news:0b2201c39efb$cb846fe0$a601...@phx.gbl...