Convert Text to number

[Silvio]

How can I convert a text field to numeric value?

The text values are:
< 35
<15
1.5
0.015

9900

The desired result need to be:
35
15
1.5
0.015
9900

Then in a separate field I need to store the > (Greater than) and < (less
than) signs from the same text filed so:

< 35 (please note the space between the < and the number 35)
<15 (please note that there is no space between < and the number 15)
1.5
0.015

9900

Will give me:
<
<
(null)
(null)
Thank you,
Silvio

 

[Jerry Whittle]

In a query:

TheNumber: IIf([TheField]=">" Or "<",Val(Mid([TheField],2)),Val([TheField]))

TheSign: IIf(Left([TheField],1)=">" Or
Left([TheField],1)="<",Left([TheField],1),Null)

 

[John W. Vinson]

How can I convert a text field to numeric value?

The text values are:
< 35
<15
1.5
0.015

The desired result need to be:
35
15
1.5
0.015
9900

Then in a separate field I need to store the > (Greater than) and < (less
than) signs from the same text filed so:

< 35 (please note the space between the < and the number 35)
<15 (please note that there is no space between < and the number 15)
1.5
0.015

Will give me:
<
<
(null)
(null)

Assuming that this field is named MyText, add a new Number (Double) or
Currency field named MyNum and text field GTLT. You can then run an update
query like

UPDATE MyTable
SET MyNum=Val(Replace(Replace(Replace([MyText], "<", ""), ">", ""), " ", "")),
GTLT = IIF(InStr([MyText], ">") > 0, ">", IIF(InStr([MyText], "<") > 0, "<",
Null))

Use CCur() instead of Val() if you decide on a Currency field (which will
handle exactly four decimal places).

 

[Silvio]

Hi Jerry thank you for the help. However, most number seem to convert
correctly except number like:

Original Conversion Should be
4.2 0.2 4.2
530 30 530
73 3 73

it appears that numbers without sign in from have trouble.

In a query:

TheNumber: IIf([TheField]=">" Or "<",Val(Mid([TheField],2)),Val([TheField]))

TheSign: IIf(Left([TheField],1)=">" Or
Left([TheField],1)="<",Left([TheField],1),Null)

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

How can I convert a text field to numeric value?

The text values are:
< 35
<15
1.5
0.015

The desired result need to be:
35
15
1.5
0.015
9900

Then in a separate field I need to store the > (Greater than) and < (less
than) signs from the same text filed so:

< 35 (please note the space between the < and the number 35)
<15 (please note that there is no space between < and the number 15)
1.5
0.015

Will give me:
<
<
(null)
(null)

Thank you,
Silvio

 

[Silvio]

Thanks John, but updating is not an option and the numbers are not currency.

How can I convert a text field to numeric value?

The text values are:
< 35
<15
1.5
0.015

The desired result need to be:
35
15
1.5
0.015
9900

Then in a separate field I need to store the > (Greater than) and < (less
than) signs from the same text filed so:

< 35 (please note the space between the < and the number 35)
<15 (please note that there is no space between < and the number 15)
1.5
0.015

Will give me:
<
<
(null)
(null)

Assuming that this field is named MyText, add a new Number (Double) or
Currency field named MyNum and text field GTLT. You can then run an update
query like

UPDATE MyTable
SET MyNum=Val(Replace(Replace(Replace([MyText], "<", ""), ">", ""), " ", "")),
GTLT = IIF(InStr([MyText], ">") > 0, ">", IIF(InStr([MyText], "<") > 0, "<",
Null))

Use CCur() instead of Val() if you decide on a Currency field (which will
handle exactly four decimal places).

 

[Jerry Whittle]

I sure messed that up. See if this works better.

TheNumber: IIf(Left([TheField],1)=">" Or Left([TheField],1)="<",
Val(Mid([TheField],2)), Val([TheField]))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry thank you for the help. However, most number seem to convert
correctly except number like:

Original Conversion Should be
4.2 0.2 4.2
530 30 530
73 3 73

it appears that numbers without sign in from have trouble.

In a query:

TheNumber: IIf([TheField]=">" Or "<",Val(Mid([TheField],2)),Val([TheField]))

TheSign: IIf(Left([TheField],1)=">" Or
Left([TheField],1)="<",Left([TheField],1),Null)

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

How can I convert a text field to numeric value?

The text values are:
< 35
<15
1.5
0.015
9900

The desired result need to be:
35
15
1.5
0.015
9900

Then in a separate field I need to store the > (Greater than) and < (less
than) signs from the same text filed so:

< 35 (please note the space between the < and the number 35)
<15 (please note that there is no space between < and the number 15)
1.5
0.015
9900

Will give me:
<
<
(null)
(null)


Thank you,
Silvio

 

[Silvio]

Yes this work! Thank you a million!

I sure messed that up. See if this works better.

TheNumber: IIf(Left([TheField],1)=">" Or Left([TheField],1)="<",
Val(Mid([TheField],2)), Val([TheField]))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry thank you for the help. However, most number seem to convert
correctly except number like:

Original Conversion Should be
4.2 0.2 4.2
530 30 530
73 3 73

it appears that numbers without sign in from have trouble.

In a query:

TheNumber: IIf([TheField]=">" Or "<",Val(Mid([TheField],2)),Val([TheField]))

TheSign: IIf(Left([TheField],1)=">" Or
Left([TheField],1)="<",Left([TheField],1),Null)

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

How can I convert a text field to numeric value?

The text values are:
< 35
<15
1.5
0.015
9900

The desired result need to be:
35
15
1.5
0.015
9900

Then in a separate field I need to store the > (Greater than) and < (less
than) signs from the same text filed so:

< 35 (please note the space between the < and the number 35)
<15 (please note that there is no space between < and the number 15)
1.5
0.015
9900

Will give me:
<
<
(null)
(null)


Thank you,
Silvio

 

[Silvio]

Jerry, one more piece to this and I am done: if the[TheFiled] does not have
any numeric value in it, right now the query place a 0 (zero) in it, I would
like it to be null instead. How?

I sure messed that up. See if this works better.

TheNumber: IIf(Left([TheField],1)=">" Or Left([TheField],1)="<",
Val(Mid([TheField],2)), Val([TheField]))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry thank you for the help. However, most number seem to convert
correctly except number like:

Original Conversion Should be
4.2 0.2 4.2
530 30 530
73 3 73

it appears that numbers without sign in from have trouble.

In a query:

TheNumber: IIf([TheField]=">" Or "<",Val(Mid([TheField],2)),Val([TheField]))

TheSign: IIf(Left([TheField],1)=">" Or
Left([TheField],1)="<",Left([TheField],1),Null)

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

How can I convert a text field to numeric value?

The text values are:
< 35
<15
1.5
0.015
9900

The desired result need to be:
35
15
1.5
0.015
9900

Then in a separate field I need to store the > (Greater than) and < (less
than) signs from the same text filed so:

< 35 (please note the space between the < and the number 35)
<15 (please note that there is no space between < and the number 15)
1.5
0.015
9900

Will give me:
<
<
(null)
(null)


Thank you,
Silvio

 

[John Spencer]

Try

TheNumber: IIF([TheField] Like "[<>]*",
,IIF(IsNumeric(Mid([TheField,2)),VAL(Mid([TheField,2)),Null)
,IIF(IsNumeric([TheField]),Val([TheField]),Null))

TheSign: IIF([TheField] Like "[<>]*",Left([TheField],1),Null)

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

Jerry, one more piece to this and I am done: if the[TheFiled] does not have
any numeric value in it, right now the query place a 0 (zero) in it, I would
like it to be null instead. How?

I sure messed that up. See if this works better.

TheNumber: IIf(Left([TheField],1)=">" Or Left([TheField],1)="<",
Val(Mid([TheField],2)), Val([TheField]))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry thank you for the help. However, most number seem to convert
correctly except number like:

Original Conversion Should be
4.2 0.2 4.2
530 30 530
73 3 73

it appears that numbers without sign in from have trouble.





:

In a query:

TheNumber: IIf([TheField]=">" Or "<",Val(Mid([TheField],2)),Val([TheField]))

TheSign: IIf(Left([TheField],1)=">" Or
Left([TheField],1)="<",Left([TheField],1),Null)

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

How can I convert a text field to numeric value?

The text values are:
< 35
<15
1.5
0.015
9900
The desired result need to be:
35
15
1.5
0.015
9900

Then in a separate field I need to store the > (Greater than) and < (less
than) signs from the same text filed so:

< 35 (please note the space between the < and the number 35)
<15 (please note that there is no space between < and the number 15)
1.5
0.015
9900
Will give me:
<
<
(null)
(null)
Thank you,
Silvio