Data Type Change

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vtj - 28 Feb 2006 16:09 GMT

I have a text field that contains "1.25" and want it to be a number field.
It errors on Data Type mismatch when I try to put it in a numeric field. Is
there any way to get text to be numeric so arithmetic can be done? Thanks
for the help!

Reply to this Message

RoyVidar - 28 Feb 2006 16:19 GMT

vtj wrote in message
<8F1C3949-CF6C-4323-8EF9-AD46408A4454@microsoft.com> :

> I have a text field that contains "1.25" and want it to be a number field.
> It errors on Data Type mismatch when I try to put it in a numeric field. Is
> there any way to get text to be numeric so arithmetic can be done? Thanks
> for the help!

I don't know exactly what you ask, but look up the functions Csng,
Cdbl,
CCur, CDec according to the datatype you're converting too under the
help topic Type Conversion Functions. Then there's also the Val
function. See if any of this helps, or post back with a bit more info
on how/what you're doing.

Signature

Roy-Vidar

Reply to this Message

vtj - 28 Feb 2006 18:05 GMT

The Val function works!! thanks!!

> vtj wrote in message
> <8F1C3949-CF6C-4323-8EF9-AD46408A4454@microsoft.com> :
[quoted text clipped - 9 lines]
> function. See if any of this helps, or post back with a bit more info
> on how/what you're doing.

Reply to this Message

John Spencer - 28 Feb 2006 18:25 GMT

Val works as long as you don't have a null value.

Also it returns Zero for strings that can't be interpreted as numbers. If
you have "99Times" it will return 99.

I prefer something a little more complex to ensure I am only converting
fields that are all numbers.

IIF(IsNumeric([SomeField]),Val([SomeField]),Null)

Even that can have some problems as IsNumeric will see "111e2" as a number
expressed with scientific notation.

> The Val function works!! thanks!!
>
[quoted text clipped - 14 lines]
>> function. See if any of this helps, or post back with a bit more info
>> on how/what you're doing.

Reply to this Message

RoyVidar - 28 Feb 2006 18:41 GMT

John Spencer wrote in message <Ook#PRJPGHA.1040@TK2MSFTNGP12.phx.gbl> :

> Even that can have some problems as IsNumeric will see "111e2" as a number
> expressed with scientific notation.

Don't forget the same also goes for "111D2" ;-)

Signature

Roy-Vidar

Reply to this Message

Brendan Reynolds - 28 Feb 2006 23:36 GMT

Another potential gotcha is currency symbols. On my system ...

? isnumeric("¤1")
True
? isnumeric("$1")
False

The symbol in the first example is the Euro symbol. If you run that code on
a PC configured with typical US settings, you'll get the opposite result.

Signature

Brendan Reynolds
Access MVP

> John Spencer wrote in message <Ook#PRJPGHA.1040@TK2MSFTNGP12.phx.gbl> :
>> Even that can have some problems as IsNumeric will see "111e2" as a
>> number expressed with scientific notation.
>
> Don't forget the same also goes for "111D2" ;-)

Reply to this Message