Strip $ and , from a Text field

[h2fcell]

Hello,
Using Access 2007, I’m trying to create a query that converts a field coming
from an Excel file link into a number. The Excel field contains Text in the
following form.
$ 1,394.45
$ 455.00
$ 23,080.00
There is a space between the $ and the first digit.
I would like the query to change them to numbers like:
1394.45
455
23080
I have the following but it fails when it reaches a number with a comma.
Expr3: Val(IIf(Len([Total Sale])>0,Right([Total Sale],(Len([Total
Sale])-2))," "))
I guess I really asking how do I strip $ and , from a Text field?
or
Is there an easier way?
Thanks

 

[h2fcell]

Here's my new creation that works.
Expr6: Val(IIf(Len([Total Sale])>0,Replace(Replace(Replace([Total Sale],"
",""),"$",""),",",""),0))

I would still like to know if there's a better way?
Thanks.

 

[KARL DEWEY]

Try this --
CDbl([Total Sale])

--
Build a little, test a little.

Here's my new creation that works.
Expr6: Val(IIf(Len([Total Sale])>0,Replace(Replace(Replace([Total Sale],"
",""),"$",""),",",""),0))

I would still like to know if there's a better way?
Thanks.

Hello,
Using Access 2007, I’m trying to create a query that converts a field coming
from an Excel file link into a number. The Excel field contains Text in the
following form.
$ 1,394.45
$ 455.00
$ 23,080.00
There is a space between the $ and the first digit.
I would like the query to change them to numbers like:
1394.45
455
23080
I have the following but it fails when it reaches a number with a comma.
Expr3: Val(IIf(Len([Total Sale])>0,Right([Total Sale],(Len([Total
Sale])-2))," "))
I guess I really asking how do I strip $ and , from a Text field?
or
Is there an easier way?
Thanks

 

[John Spencer]

I would use a slightly more complex expression to avoid errors.

IIF(IsNumeric([Total Sale]),CDbl([Total Sale]),Null)

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

 

[Michael Conroy]

Your spreadsheet is useless with numbers as text. My suggestion would be to
use =Value() in Excel which will turn the text $ 123.45 into a number 123.45.
Then just reference the number column in your link.