data manipulation in SQL Passthrough query

A

Andreas

Hello,

I have a pass-through query (seen below). The field
"fpmcaps.x_fpmrequestdata.data" is kind of a data blob that contains
various fields. I am trying to split the blob (see middle section), but
access gives me the error message that the right parenthesis is
missing.

SELECT fpmcaps.x_fpmrequest.receivername,
fpmcaps.x_fpmrequestdata.data,(fpmcaps.x_fpmrequest.settlementamount/100)
AS Amount, fpmcaps.x_fpmrequest.sendername AS sender,

IIf(InStr(1,fpmcaps.x_fpmrequestdata.data,'<beneficiaryName>')=0,Null,Mid(fpmcaps.x_fpmrequestdata.data,(InStr(1,fpmcaps.x_fpmrequestdata.data,'<beneficiaryName>')+17),(InStr(1,fpmcaps.x_fpmrequestdata.data,'</beneficiaryName>')-(InStr(1,fpmcaps.x_fpmrequestdata.data,'<beneficiaryName>')+17))))
AS BeneName

FROM fpmcaps.x_fpmrequest INNER JOIN fpmcaps.x_fpmrequestdata ON
fpmcaps.x_fpmrequest.inputid = fpmcaps.x_fpmrequestdata.request_inputid

WHERE fpmcaps.x_fpmrequest.statename =
'IN_WORKFLOW/DEEP_IN_WORKFLOW/SANCTION_HOLD' AND
fpmcaps.x_fpmrequest.customproperty1 = 'FP'

Anyone any comments on why I would receive this message?

Thanks,

Andreas
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Query limitations 4
Split Data 0
update query to remove parenthesis 5
Switch formula in query results in "Text too long to be edited." 3
Data Mismatch in Query Criteria Inconsistency 0
Query in a DAP without the popup 1
Parsing Memo for date problem 8
Slow Custom Functions calling to Passthrough Query 2

Top