Converting Text to Number

L

Lucien

I want to convert Text records to Number within a query. My table is too big
to change the data type in the table itself. Someone told me to use the
expression builder to create this expression:

Expr1: [MasterAvailabilityData]![ActualAvailPlant]*1

This did not work. I need to convert this one field to Number because I
have another expression that is comparing this field with another. It wont
work because they are different data types. Any suggestions?
 
K

KARL DEWEY

If it is a text field that contains "10" and not "Ten" then just use the Val
function.
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the convert functions:

CInt(value) - convert the value to integer
CDbl(value) - convert the value to double
CLng(value) - convert the value to long

.... etc. ...

Usage:

If CInt(text_value) = Integer_value Then ...

or

WHERE Double_Column = CDbl(text_value)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+zh5IechKqOuFEgEQIgGACfSivbtSetC1L/YaY6nurUzyS2uAUAmQGA
zZmD6tBHvyzMoT90K4nmh6fT
=5RKD
-----END PGP SIGNATURE-----
 
M

Marshall Barton

Lucien said:
I want to convert Text records to Number within a query. My table is too big
to change the data type in the table itself. Someone told me to use the
expression builder to create this expression:

Expr1: [MasterAvailabilityData]![ActualAvailPlant]*1

This did not work. I need to convert this one field to Number because I
have another expression that is comparing this field with another. It wont
work because they are different data types.
Click to expand...


Not really clear what you really need here. If the field,
ActualAvailPlant really is converable to a number (what kind
of number?), then you can just use:
Expr1: Val(ActualAvailPlant)

But, that's not really necessary. You can just use
Val(ActualAvailPlant) in the comparison or where ever you
really need to use it in an expression.
 
L

Lucien

thanks for the help!



Marshall Barton said:
Lucien said:
I want to convert Text records to Number within a query. My table is too big
to change the data type in the table itself. Someone told me to use the
expression builder to create this expression:

Expr1: [MasterAvailabilityData]![ActualAvailPlant]*1

This did not work. I need to convert this one field to Number because I
have another expression that is comparing this field with another. It wont
work because they are different data types.
Click to expand...


Not really clear what you really need here. If the field,
ActualAvailPlant really is converable to a number (what kind
of number?), then you can just use:
Expr1: Val(ActualAvailPlant)

But, that's not really necessary. You can just use
Val(ActualAvailPlant) in the comparison or where ever you
really need to use it in an expression.
Click to expand...
 
Top