Convert text to a number

  • Thread starter Thread starter Ron
  • Start date Start date
R

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.
 
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.
Click to expand...

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.
Click to expand...

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?
Click to expand...

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


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.
 
Back
Top