Convert Short Text Field to Numeric

[CBROWN1]

Hi-
I am trying to convert a short text field to a numeric field in access. My data looks like the following

$1,237
$5,085
$767
($389)
($3,456)

I have tried to use the CDbl function {New_Field_Name: CDbl(Mid([USD],2,30))} and this works except where the value is a negative like ($389) and ($3,456) above.
I have also tried formatting the field New_Field_Name: Format([Table1]![Field_Name],"Standard") but to no joy.

Any suggestions will be greatly appreciated.

Thanks
C

 

[mrmmickle1]

Maybe try using a formula similar to this.... I just tested with your sample data and the arithmetic operation is performed correctly for negative and positive numbers....

[COLOR=#0000ff]Sub[/COLOR] Test()


 [COLOR=#0000ff]   Dim[/COLOR] myTestVar [COLOR=#0000ff]As String[/COLOR]
    
    myTestVar = Range("A5")
    myTestVar = CDbl(Replace(Replace(Replace(myTestVar, "$", ""), ")", ""), "(", "-"))
    myTestVar = myTestVar + 5
    
    MsgBox myTestVar


[COLOR=#0000ff]End Sub[/COLOR]

Excel 2010
	A
1	$1,237
2	$5,085
3	$767
4	($389)
5	($3,456)
Sheet1

This manipulation should work in Access.

 

[CBROWN1]

Thank you so much.....a great example of me over complicating matters! Your suggestion works perfectly!

 

[mrmmickle1]

Glad to assist. Have a great day

 

[xenou]

Strange CDbl() works for me in all of the above cases.