Add MS Access Replace() function to prm files

building semantic layer
1

Hi Members,

Can anyone provide the code for adding the MS Access replace() function to the prm files, I’m going to try and guess at the code but rather someone could provide a copy if they have it working!

I have tried a few variations but cannot get the function to work.

Many thanks Gurus

Woomaster :uk: (BOB member since 2010-04-16)

2

Hi All,

This is as far as I have reached and i still get unrecognised function when parsing. I’ve tried as syntax {fn replace($1,$2,$3)} but this did not work either.


<Function Group="False" ID="Replace" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
		<Argument Type="String"></Argument>
		<Argument Type="String"></Argument>
            </Arguments>
            <SQL>replace($1,$2,$3)</SQL>
        </Function>

Does this code look right - also must I have this prm change on the server before I can use it in designer?

Woomaster :uk: (BOB member since 2010-04-16)

3

:wah: :wah: BUMP :wah: :wah: :wah:

Woomaster :uk: (BOB member since 2010-04-16)

4

Whether the code is in the .PRM file or not has nothing to do with whether it will parse or not. Parsing is strictly a database operation. You can use any function available - as long as you have the proper syntax. But it has to be a database function, not a VBA function. If you can’t do the select directly in MS Access, then it won’t work from Designer, even if you do put it into the parameter file.

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Hi Guru Dave,

Many thanks for that - now this is where I am confused as I can use the replace function in MS Access. However I cannot use the same code in designer it just doesn’t work. As far as I know Replace() is not a VBA function it is a built in MS Access Function that can be called up in the query panel.

The error I consistently get is “undefined function”.

For instance for a given field “Name” in table “Names” if I wish to replace the letter R with D in the string “Rave” in MS Access I use the syntax

 Name: Replace([Names]![Name],"R","D")

However if I use this code in Designer against the same DB, It won’t parse and it won’t run in webi (XI R3)

I don’t know if anyone knows the answer perhaps replace is not a DB function?

Many thanks for your help :wave:

Woomaster :uk: (BOB member since 2010-04-16)

6

Replace certainly appears to be a VB function based on what I found via Google. The fact that it’s not working in SQL would be a second indication. :wink: On the other hand, I did find one article that showed a sample with SQL code.

Needless to say, I can say with confidence that it’s not the lack of the syntax in the .PRM file that’s keeping it from working.

Dave Rathbun :us: (BOB member since 2002-06-06)

7

Hi Dave,

Many thanks again for your reply. The example given in the query panel in MS Access on the link you posted is exactly the same syntax I have used. Therefore as the Replace() function appears to be an MS Access function could the issue lie with the MS Access ODBC driver not being able to perform the translation?

Although I have found this article that suggests it’s a known issue with workarounds! http://support.microsoft.com/kb/q189448/

Woomaster :uk: (BOB member since 2010-04-16)