How can I (or can I) change an existing table field from text to numeric?

I need to use the text field for some calculations. It is a large file and
i'm using Access 2007.


Thanks,
Jennifer

Permanent change or just change for purposes of calculation. Assuming that
the field may contain non-numeric data, use
IIF(IsNumeric([SomeField]),Val([SomeField]),Null)

If the field is never null and always contains only numbers then you can use
one of the conversion functions
CDbl([SomeField])

If you want to change the datatype permanently, you can try opening the table
and changing the datatype. BACKUP your data first, in case this goes wrong.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Floydene wrote:
> How can I (or can I) change an existing table field from text to numeric?
>
> I need to use the text field for some calculations. It is a large file and
> i'm using Access 2007.
>
>
> Thanks,
> Jennifer

I did try changing the datatype and lost the data. (I'mworking in a test file
first). Unfortunately, I am not comfortable in SQL - I'm presuming that the
solutions you gave are for queries to change the data?

Thank you,



"John Spencer" wrote:

> Permanent change or just change for purposes of calculation. Assuming that
> the field may contain non-numeric data, use
> IIF(IsNumeric([SomeField]),Val([SomeField]),Null)
>
> If the field is never null and always contains only numbers then you can use
> one of the conversion functions
> CDbl([SomeField])
>
> If you want to change the datatype permanently, you can try opening the table
> and changing the datatype. BACKUP your data first, in case this goes wrong.
>
> John Spencer
> Access MVP 2002-2005, 2007-2008
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Floydene wrote:
> > How can I (or can I) change an existing table field from text to numeric?
> >
> > I need to use the text field for some calculations. It is a large file and
> > i'm using Access 2007.
> >
> >
> > Thanks,
> > Jennifer
>

What do you mean by "use the text field for calculations"

Calculations are normally done with numeric data.

Can you describe what you are wanting to do? Perhaps we can offer some
suggestions.

"Floydene" <(E-Mail Removed)> wrote in message
news:A931C074-CCDA-4279-8F23-(E-Mail Removed)...
>I did try changing the datatype and lost the data. (I'mworking in a test
>file
> first). Unfortunately, I am not comfortable in SQL - I'm presuming that
> the
> solutions you gave are for queries to change the data?
>
> Thank you,
>
>
>
> "John Spencer" wrote:
>
>> Permanent change or just change for purposes of calculation. Assuming
>> that
>> the field may contain non-numeric data, use
>> IIF(IsNumeric([SomeField]),Val([SomeField]),Null)
>>
>> If the field is never null and always contains only numbers then you can
>> use
>> one of the conversion functions
>> CDbl([SomeField])
>>
>> If you want to change the datatype permanently, you can try opening the
>> table
>> and changing the datatype. BACKUP your data first, in case this goes
>> wrong.
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2008
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>> Floydene wrote:
>> > How can I (or can I) change an existing table field from text to
>> > numeric?
>> >
>> > I need to use the text field for some calculations. It is a large file
>> > and
>> > i'm using Access 2007.
>> >
>> >
>> > Thanks,
>> > Jennifer
>>

Well, I have a solution. I converted using the Val function. Now I can
perform the calculations.

Thanks for your reply.

"Klatuu" wrote:

> What do you mean by "use the text field for calculations"
>
> Calculations are normally done with numeric data.
>
> Can you describe what you are wanting to do? Perhaps we can offer some
> suggestions.
>
> "Floydene" <(E-Mail Removed)> wrote in message
> news:A931C074-CCDA-4279-8F23-(E-Mail Removed)...
> >I did try changing the datatype and lost the data. (I'mworking in a test
> >file
> > first). Unfortunately, I am not comfortable in SQL - I'm presuming that
> > the
> > solutions you gave are for queries to change the data?
> >
> > Thank you,
> >
> >
> >
> > "John Spencer" wrote:
> >
> >> Permanent change or just change for purposes of calculation. Assuming
> >> that
> >> the field may contain non-numeric data, use
> >> IIF(IsNumeric([SomeField]),Val([SomeField]),Null)
> >>
> >> If the field is never null and always contains only numbers then you can
> >> use
> >> one of the conversion functions
> >> CDbl([SomeField])
> >>
> >> If you want to change the datatype permanently, you can try opening the
> >> table
> >> and changing the datatype. BACKUP your data first, in case this goes
> >> wrong.
> >>
> >> John Spencer
> >> Access MVP 2002-2005, 2007-2008
> >> The Hilltop Institute
> >> University of Maryland Baltimore County
> >>
> >> Floydene wrote:
> >> > How can I (or can I) change an existing table field from text to
> >> > numeric?
> >> >
> >> > I need to use the text field for some calculations. It is a large file
> >> > and
> >> > i'm using Access 2007.
> >> >
> >> >
> >> > Thanks,
> >> > Jennifer
> >>
>
>
>

The solutions can be used in a query as a calculated field or they can be used
on a form or report.

If you wanted to permanently change the data, you could try an update query.

Add a new field that is the proper datatype Number - Double (for instance)
Make sure you set the default for the field to blank unless you want a default
value of zero.

UPDATE YourTable
Set [TheNewField] = CDbl([TheOldField])
WHERE IsNumeric([TheOldField])

In the query grid (Design view)
Add your table
Add the new field
in a blank cell enter
IsNumeric([TheOldField])
Under that enter
TRUE
Select Query: Update from the menu
In the UPDATE TO cell under the new field enter
CDbl([TheOldField])
Select Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Floydene wrote:
> I did try changing the datatype and lost the data. (I'mworking in a test file
> first). Unfortunately, I am not comfortable in SQL - I'm presuming that the
> solutions you gave are for queries to change the data?
>
> Thank you,
>
>
>
> "John Spencer" wrote:
>
>> Permanent change or just change for purposes of calculation. Assuming that
>> the field may contain non-numeric data, use
>> IIF(IsNumeric([SomeField]),Val([SomeField]),Null)
>>
>> If the field is never null and always contains only numbers then you can use
>> one of the conversion functions
>> CDbl([SomeField])
>>
>> If you want to change the datatype permanently, you can try opening the table
>> and changing the datatype. BACKUP your data first, in case this goes wrong.
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2008
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>> Floydene wrote:
>>> How can I (or can I) change an existing table field from text to numeric?
>>>
>>> I need to use the text field for some calculations. It is a large file and
>>> i'm using Access 2007.
>>>
>>>
>>> Thanks,
>>> Jennifer

On Thu, 13 Nov 2008 10:31:03 -0800, Floydene
<(E-Mail Removed)> wrote:

>How can I (or can I) change an existing table field from text to numeric?
>
>I need to use the text field for some calculations. It is a large file and
>i'm using Access 2007.

It may not be necessary; you can use the Val() function to convert a text
field into a number value for the purpose of calculations.

If you do need to do so, or if a Number datatype is more appropriate for your
data, then you can change it. MAKE A BACKUP!! first! Then open the table in
design view and just change the datatype in the field properties.

If the table is very large (hundreds of thousands of records) you may get an
out of memory error, or other possible problems. If so, you may want to copy
the table to a new table, *design mode only*, so you have a new, empty table.
Change the datatype in this table using the tables design window, and then run
an Append query to migrate the data from the old table into the new one. Break
any relationships to the old table and reestablish them to the new one. Then
rename the old table (say to tablename_OLD) and the new table to the old name.
Test everything; when you're sure the new table works, delete the old table
and Compact the database to recover the space it once occupied.
--

John W. Vinson [MVP]