Pass-Through Query in Access

Topic

New Reply

I have an expression written in Access
Mid([CBP], (InStr(5,[CBP],”~”)+1), (IIf((InStr((InStr(5,[CBP],”~”)+1),[CBP],”~”)-(InStr(5,[CBP],”~”)+1))>0,(InStr((InStr(5,[CBP],”~”)+1),[CBP],”~”)-(InStr(5,[CBP],”~”)+1)),0)))

What I need to do is translate this into SQL so I can use it in a pass-through query connecting to an Oracle database.

I have the syntax down for everything except the “IIF”. Is there an equivalent function in SQL that can be used to complete this expression?

Thanks for any help.

Reply | Quote

Replies

Richard,

While editing the query just switch to SQL view to see how to code it. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I continued to play with it and was able to get this code to work.

(CASE WHEN ((InStr(CBP,’~’,2,2))-(InStr(CBP,’~’,2)))>0
THEN SUBSTR(CBP, (InStr(CBP, ‘~’, 2))+1, ( (InStr(CBP, ‘~’, 2, 2)) – (InStr(CBP, ‘~’, 2))-1 ) )
ELSE NULL
END )

Reply | Quote