REPLACE function

[BeataG]

Hello,

I've problem with REPLACE function.

I'd like to use it for strings or substrings that are placed in the column XYZ.
If there is substring : "Iii" then I'd like it to be changed with "III", if there is substring "Ii" then my goal is to receive "II".

Could you help me in writing the proper VBA module for this problem?
Thanks in advance.

 

[jal]

Partly depends on whether or not a given record can have multiple instances of "Iii" embedded in a single string. The Replace function only replaces the first instance, as I recall.

DoCmd.RunSql "UPDATE table1 SET XYZ = Replace(XYZ, 'Iii', 'III')"

 

[jal]

Will there be multiple instances?

 

[BeataG]

Will there be multiple instances?

No, there is always single occurrence of "Iii" or "Ii" substring in a given record. Additionally, one record contains or "Ii" or "Iii" substring or such one that doesn't need to be changed at all.
There is no case when one record contains both "Ii" and "Iii" and when it contains multiple cases of substrings mentioned above.

Ah, and it will be a query, not a table.
Thanks.

 

[jal]

Hello,

I've problem with REPLACE function.

I'd like to use it for strings or substrings that are placed in the column XYZ.
If there is substring : "Iii" then I'd like it to be changed with "III", if there is substring "Ii" then my goal is to receive "II".

Could you help me in writing the proper VBA module for this problem?
Thanks in advance.

Well, okay, if there's only one instance, then probably the only thing that matters is making sure to do the larger replacment first. It's just two lines of code, it seems to me.

DoCmd.RunSql "UPDATE table1 SET XYZ = Replace(XYZ, 'Iii', 'III')"
DoCmd.RunSql "UPDATE table1 SET XYZ = Replace(XYZ, 'Ii', 'II')"

Personally I use Forms more than "modules". I open a new form in Design view, then drop a command button on the form (cancelling out the button wizards), I change the button caption to 'Process Data', change the name of the button to btnProcess, I look for the button Event called 'On_Click'. Select 'Event Procedure' and click the ellipsis to open up the handler sub:

Private Sub btnProcess_Click()


End Sub


Then I just insert my code:



Private Sub btnProcess_Click()

DoCmd.RunSql "UPDATE table1 SET XYZ = Replace(XYZ, 'Iii', 'III')"
DoCmd.RunSql "UPDATE table1 SET XYZ = Replace(XYZ, 'Ii', 'II')"

End Sub

Now when the user clicks the button, the code will run.

That's just my style. Generally I use a standard module only for a UDF (user-defined function), or I'll use a class module to organize my code.

 

[BeataG]

Thank you very much, it works perfectly. Thanks again.