Access Expression / Pull Out Number and currency (1 Viewer)

[smoothlarryhughes]

Can anyone help me with an expression (preferably with built in functions) to pull the currency and dollar amounts out of the reference field? Examples below:

Reference	MONEY	CURRENCY
/ACOM/43958,90EUR0,00	43958.90	EUR
/ACOM/48761,41USD0,00	48761.41	USD
/ACOM/1212,96USD0,00	1212.96	USD
/ACOM/702,89USD0,00	702.89	USD
/ACOM/222,50USD0,00	222.50	USD
/ACOM/6196,43EUR0,00	6196.43	EUR
/ACOM/208,16EUR0,00	208.16	EUR
/ACOM/7006,67EUR0,00	7006.67	EUR

 

[June7]

Assuming currency is always 3 characters:

Cur: Right(Left(x, InStrRev(x,",")-2),3)

Assuming string always begins with "/ACOM/" and Access will recognize the comma in place of period as decimal which it should if you are running a non-U.S. version of Access:

Amt: Val(Mid(x,7))

However, since you show period in result I must presume you are not. So this gets tricky.

Amt: Val(Replace(Mid(x,7), ",", "."))

 

[Micron]

Val will not deal with comma separators or currency signs (not that there are any here)
I was thinking
Replace(Mid("/ACOM/7006,67EUR0,00",Instr(1,"/ACOM/7006,67EUR0,00",",")-1),",00","")

I'm probably not understanding exactly which is the currency part as 7006,67 looks odd to me as a currency value. Some expected results would have helped.

 

[June7]

Expected results are showing in OP. I revised my answer. Tested and works.

 

[smoothlarryhughes]

Perfect! Thanks!