Convert Text to Number

[mattc66 via AccessMonster.com]

Hi All,

I have data that is coming from an external source. I have to strip out a
number out of a text field. However I need to then link this number to actual
number field in another table.

Is there an SQL statment that I could use to convert the text to a number?

Below is the SQL view of my table. The field I need as a number is the QUOTE2.


SELECT OEDET_C2.ORDER_NUM, OEDET_C2.DETAIL_LINE_NUM, OEDET_C2.DESCRIPTION,
OEDET_C2.DETAIL_LINE_STATUS, Left([DESCRIPTION],5) AS Quotetyp, Trim(Mid(
[Description],InStrRev([Description]," ")+1)) AS Quote2
FROM OEORD_C2 INNER JOIN OEDET_C2 ON OEORD_C2.ORDER_NUM = OEDET_C2.ORDER_NUM
WHERE (((Left([DESCRIPTION],5)) Like "Quote*"));



Thanks
Matt

 

[raskew via AccessMonster.com]

The Val() function should do it for you.

...Val(Trim(Mid(
[Description],InStrRev([Description]," "))+1)) AS Quote2

Bob

Hi All,

I have data that is coming from an external source. I have to strip out a
number out of a text field. However I need to then link this number to actual
number field in another table.

Is there an SQL statment that I could use to convert the text to a number?

Below is the SQL view of my table. The field I need as a number is the QUOTE2.

SELECT OEDET_C2.ORDER_NUM, OEDET_C2.DETAIL_LINE_NUM, OEDET_C2.DESCRIPTION,
OEDET_C2.DETAIL_LINE_STATUS, Left([DESCRIPTION],5) AS Quotetyp, Trim(Mid(
[Description],InStrRev([Description]," ")+1)) AS Quote2
FROM OEORD_C2 INNER JOIN OEDET_C2 ON OEORD_C2.ORDER_NUM = OEDET_C2.ORDER_NUM
WHERE (((Left([DESCRIPTION],5)) Like "Quote*"));

Thanks
Matt

 

[John W. Vinson]

Below is the SQL view of my table. The field I need as a number is the QUOTE2.

Just wrap it one layer deeper:

Val(Trim(Mid([Description],InStrRev([Description]," ")+1))) AS Quote2

Val() will convert a string of digits (or decimals, or exponential -
"2.9979E10" is a valid number) to a number datatype.

John W. Vinson [MVP]