change a field of text "AU $9.95" to currency "9.95"

[Andrew]

i have a imported data in a table which has a field [sales price] of text "AU
$ **.**" and i need to do calculation on the field. I have created a form
with the exact table and trying to link the field [sales] of currency to the
field of [sales price]. What can i do?
Thanks

 

[John W. Vinson]

i have a imported data in a table which has a field [sales price] of text "AU
$ **.**" and i need to do calculation on the field. I have created a form
with the exact table and trying to link the field [sales] of currency to the
field of [sales price]. What can i do?
Thanks

You can use a calculated field:

SalesAsNumber: Val(Mid([sales price], 3))


John W. Vinson [MVP]

 

[Andrew]

i tried a few options here like that but it always gives me #Name? error.
I have a new table exactly the same as the [ebay report] but in the field of
[sales price] i have a field of currency with control source =
CCur(Val(Mid([ebay report]![Sale Price],4)))
can you help?

i have a imported data in a table which has a field [sales price] of text "AU
$ **.**" and i need to do calculation on the field. I have created a form
with the exact table and trying to link the field [sales] of currency to the
field of [sales price]. What can i do?
Thanks

You can use a calculated field:

SalesAsNumber: Val(Mid([sales price], 3))


John W. Vinson [MVP]

 

[Frank]

Try -
CCur(Val(Mid(([ebay report]![Sale Price],5)))

Mid(([ebay report]![Sale Price],4) returns "$9.95" ... the "$" stuffs
everything ... Mid(([ebay report]![Sale Price],5) will return "9.95" and all
will work.

You might even try -
CCur(Mid(([ebay report]![Sale Price],5))
which also works.

i tried a few options here like that but it always gives me #Name? error.
I have a new table exactly the same as the [ebay report] but in the field of
[sales price] i have a field of currency with control source =
CCur(Val(Mid([ebay report]![Sale Price],4)))
can you help?

i have a imported data in a table which has a field [sales price] of text "AU
$ **.**" and i need to do calculation on the field. I have created a form
with the exact table and trying to link the field [sales] of currency to the
field of [sales price]. What can i do?
Thanks

You can use a calculated field:

SalesAsNumber: Val(Mid([sales price], 3))


John W. Vinson [MVP]