convert large text to large number

[Jennifer]

I have a 26 digit census block key as an identifier that is currently
formatted as a text field.


50001003259999996040033000

I would like to change it to a number field. Initially if I do that it wants
to write it on in scientific notation. However if I change to double fixed
with no decimal points I get the entire number however it is changing many of
the numbers to zeros.

50001003200000000000000000

Also this is a tremendously large data set (446,832 records) which is
causing some computer processing issues. Any suggestions would be appreciated.

Thanks,
Jennifer

 

[KARL DEWEY]

Why do you need to convert it to a number? Will you be doing math with it?
If no math then why is there a need to convert?

 

[Keven Denen]

I have a 26 digit census block key as an identifier that is currently
formatted as a text field.

SELECT Company.[CompanyID], Company.[customer_number], Company.[CompanyName], Company.[Address], Company.[City], Company.[County], Company.[StateOrProvince], Company.[PostalCode], Company.[Employeename], Company.[Employeename2], Company.[Employeename3], Company.[Branch], Company.[WebSite], Company..[SalesstageID], Company.[ContactName], Company.[Firstname], Company.[Title], Company.[WorkPhone], Company.[WorkExtension], Company.[HomePhone], Company.[MobilePhone], Company.[FaxNumber], Company.[EmailName], Company.[Firstcontactdate], Company.[siccode], Company.[lastupdate], Company.[ReferredBy],Company.[Notes], Company.[ContactsInterests], Company.[BuyersmodeID], Company.[ContactrankID], Company.[DeleteCompany], Company.[Currentcustomer], Company.[CustomField2], Company.[CustomField3], Company.[CustomField4], Company.[CustomField5], Company.[CustomField6], Company.[CustomField7], Company.[CustomField8], Company.[CustomField9], Company.[CustomField10], Company.[CustomField11], Company.[CustomField12], Company.[C

ustomField13], Company.[CustomField14], Company.[preparedby], Company.
[proposedear], Company.[proposecontact], Company.[CreateDate], Company.
[UniqueID], Company.[EmployeeSize], Company.[EmployeeName4], Company.
[EmployeeName5], Company.[EmployeeName6], Company.[EmployeeName7],
Company.[EmployeeName8], Company.[EmployeeName9], Company.
[EmployeeName10], Company.[AddressName], Company.[Salutation] FROM
Company WHERE Company.CompanyName Like '*' AND ((1=1) AND (IIf(InStr
([company].[postalcode]," "),Left([company].[postalcode],InStr
([company].[postalcode]," ")-1),[company].[postalcode]) IN
("TN1","TN2","TN3","TN4","TN5","TN6","TN7","TN9","TN10","TN11","TN12","TN17","TN18")
AND ((company.employeename is null) or (company.employeename = "")))
OR ((company.employeename IN ("Doug Bert")) OR (company.employeename2
IN ("Doug Bert")) OR (company.employeename3 IN ("Doug Bert")) OR
(company.employeename4 IN ("Doug Bert")) OR (company.employeename5 IN
("Doug Bert")) OR (company.employeename6 IN ("Doug Bert")) OR
(company.
employeename7 IN ("Doug Bert")) OR (company.employeename8 IN ("Doug
Bert")) OR (company.employeename9 IN ("Doug Bert")) OR
(company.employeename10 IN ("Doug Bert"))))) ORDER BY
Company.CompanyName ASC

I would like to change it to a number field. Initially if I do that it wants
to write it on in scientific notation. However if I change to double fixed
with no decimal points I get the entire number however it is changing many of
the numbers to zeros.

50001003200000000000000000

Also this is a tremendously large data set (446,832 records) which is
causing some computer processing issues. Any suggestions would be appreciated.

Thanks,
Jennifer

Use a Decimal data type, set the Precision property to 26, the Decimal
Places property to 0 and the Format property to Fixed.

Keven Denen

 

[TedMi]

But if block codes can have leading zeros and you want to display those, set
the Format proerty to a string of 26 zeros.
But I echo Karl Dewey's sentiment, asking WHY? Text fields are perfectly
acceptable as keys. If you want to ensure that users enter only digits into
a text field on an input form, that can be set with an input mask.
-TedMi


Use a Decimal data type, set the Precision property to 26, the Decimal
Places property to 0 and the Format property to Fixed.

Keven Denen

 

[Jennifer]

I am giving more consideration to why it needs to be a number. At the moment
the answer is that the data set is going to be imported into another program
(GIS) for analysis where the coinciding data field is a number and they need
to be the same.

Thanks!
Jennifer

 

[John W. Vinson]

I am giving more consideration to why it needs to be a number. At the moment
the answer is that the data set is going to be imported into another program
(GIS) for analysis where the coinciding data field is a number and they need
to be the same.

Doublecheck that requirement.

The GIS program undoubtedly requires that the value be a string of 26 digits.
It probably does *NOT* require that it be stored in Microsoft Access as a
Number datatype (of any description).

Do note also that even the Decimal datatype - the highest precision Number -
is limited to 96 bits, equivalent to some 28 decimal places.

I would be very strongly inclined to keep storing the value in Text and
exporting it to your GIS system as a string of digits. You can use an input
mask or validation rule to enforce storing a string of numeric digits.