Use query to change Text to Number

Is there way to change a field from numbers stored as text to numbers,
without deleting anything that is actual text? I would like to achieve the
same results as the GENERAL format in excel.

Dear dcozzi:

Assiming the text is always a valid number, convert it. There are different
conversions depending on the type of numeric value represented, such as
CInt, CLng, CDbl, CCur. Do this in a calculated column.

Tom Ellison


"dcozzi" wrote in message
...
Is there way to change a field from numbers stored as text to numbers,
without deleting anything that is actual text? I would like to achieve the
same results as the GENERAL format in excel.

If I understand right, the answer is no. Excel has no notion of a data
type for a column or field, while Access tables have strong data types.
The only thing you can store in a text field is text (even if it's a
string of numbers), and the only thing you can store in a numeric field
is a number.

But you can probably use an expression like this
IIF(IsNumeric([XXX]), Val(Nz([XXX], "")), Null)
to return a numeric value from a text field that contains a number, and
Null if the field contains anything else.

On Fri, 10 Mar 2006 13:36:30 -0800, dcozzi
wrote:

Is there way to change a field from numbers stored as text to numbers,
without deleting anything that is actual text? I would like to achieve the
same results as the GENERAL format in excel.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.