I have to extract numbers as currency from a text string.
Sample String: [item: 2 Yr Zero Hassle Protection] [description: Protection Plan to be mailed/emailed within 10 day] [unit_price: 12.34] [ext_price: 12.34]
See below code. As long as the string is present, no problem. But if the 12.34 (numbers) is not present I get a data mismatch error in the report. The query runs without error, but the report gives the error. The report fields are Currency. Simply, if there's no value or a null value, how do I get the report to run without error?
Code:
UnitPriceEx: IIf(
Nz([PODetail]![Descrip3],"")<>"",
Format(
Trim(
Mid(
Nz([PODetail]![Descrip3],""),InStr(Nz([PODetail]![Descrip3],""),"[unit_price: ")+13,InStr(InStr(Nz([PODetail]![Descrip3],""),"[unit_price: ")+13,Nz([PODetail]![Descrip3],""),"]")-(InStr(Nz([PODetail]![Descrip3],""),"[unit_price: ")+13)
)
)
)
,"Standard")
Thanks!