Jet SQL Replace Function

See more:

SQL

Hi all,

I'm struggling a bit with a Jet SQL coding problem. I have a column with strings in the form 'EV1.EV2' or 'EV4.EV5.EV6'. I am trying to replace the '.'s in this column with the new line delimiter '\n'. In normal SQL the Replace function could be used but the application I am using only uses Jet SQL. I have been able to solve the problem with one '.' using the code:

iif([GateCutSets].[CutSetID] like '%.%',(Left([GateCutSets].[CutSetID],InStr([GateCutSets].[CutSetID], '%.%')-1) & Chr(13) & Chr(10) & Right([GateCutSets].[CutSetID],(Len([GateCutSets].[CutSetID])-Instr([GateCutSets].[CutSetID], '%.%')))),[GateCutSets].[CutSetID])) as CutSet

This replaces the string with a substring of everything before the first '.', followed by the '\n' delimiter, followed by everything after the '.'.

When I try something similar for the situation where there are two '.' in the string however, I'm unsure how to find the position of the second '.'. Is there a function which will do this or is there an easier way of doing what I am trying to do?

All advice is appreciated.

Regards,
Stephen.