Convert Text to Numbers - Leading 0s

[Guest]

I have a column that has Account Numbers with Leading 0s. It is currently a
"Text" field. I want to convert it into a "Number" filed. When I do that by
changing the data type I lose the Account Numbers. Is there a workaround?

PS: I need to link this filed to another table that has the Account Numbers
as a "Numbers" field. Currently the link will not work.

 

[Guest]

Insert a new column for the numbers, then run a query to transfer the text
column to the numbers column, then delete the text column.
Query:
UPDATE MyTable SET MyNumber = CInt(MyText)

Back up your database FIRST!!!

-Dorian

 

[John Spencer]

Numbers NEVER have leading zeroes. So, you can add an additional field to
hold the numeric value - probably a bad idea. Why is this a bad idea, now
you have two fields to maintain the same data.

Better - use the Val function to return the numeric value. You can do that
in a query of your table and then use that to query and link it to the other
table.

Field: JustNumber: Val([AccountNumber])

Alternative solution is to use a Non-equi Join to do this. This type of
join needs to be done in SQL view and cannot be built or shown in the query
grid. Generically, that would look something like the following

SELECT [TableWithText].[AccountNumber]
, [TableWithNumbers].[SomeOtherField]
FROM [TableWithText] INNER JOIN [TableWithNumbers]
ON Val([TableWithText].[AccountNumber]) =
[TableWithNumbers].[AccountNumber]