Replace Function

[thescottsman92]

Hi



I wonder if you can help, ive been stuck on this for a while

I have a folder system that at the end of each folder system I have 4 folder (00,01,02,03) What I want to be able to do is replace the 01,02,03 with 00. I can get this function to work when I just use 01 but when I include other options lile 02 it does not work.

I have tried allsorts including writing if statements for it


Can anyone please help me and think of anything?


thanks

[JoeM]

I can get this function to work when I just use 01 but when I include other options lile 02 it does not work.

Can you post the formula that you have, so we can get a better idea of what you are trying to do?
Are you trying to do this in a calculated field in a query, in VBA, or elsewhere?

[thescottsman92]

If MsgBox("Are you sure you want to edit this file?", vbOKCancel) = vbOK Then
Me.txtarchivepath = Replace(Me.txtFolderPath, "02_3D Models", "00_Part Archive")
Else
If MsgBox("Are you sure you want to edit this file?", vbOKCancel) = vbOK Then
Me.txtarchivepath = Replace(Me.txtFolderPath, "04_Production Drawings", "00_Part Archive")
Else
MsgBox "Folder Path cannot be found"
Me.txtarchivepath = Null
End If
End If
End Sub

[JoeM]

This seems to work for me:

    Me.Refresh
    If MsgBox("Are you sure you want to edit this file?", vbOKCancel) = vbOK Then
        If InStr(Me.txtFolderPath, "02_3D Models") > 0 Then
            Me.txtArchivePath = Replace(Me.txtFolderPath, "02_3D Models", "00_Part Archive")
        Else
            If InStr(Me.txtFolderPath, "04_Production Drawings") > 0 Then
                Me.txtArchivePath = Replace(Me.txtFolderPath, "04_Production Drawings", "00_Part Archive")
            Else
                MsgBox "Folder Path cannot be found"
                Me.txtArchivePath = Null
            End If
        End If
    End If

[JoeM]

Here is an even better way using Arrays. If you have more values you are checking for, simply place them in the "myCheck" array, separated by commas. No other changes are needed to the code.

    Dim myCheck
    myCheck = Array("02_3D Models", "04_Production Drawings")
    Dim i As Integer
    Dim myArchivePath As String
    
    Me.Refresh

'   Loop through all entries in your array until you find a match
    For i = LBound(myCheck) To UBound(myCheck)
        If InStr(Me.txtFolderPath, myCheck(i)) > 0 Then
            myArchivePath = Replace(Me.txtFolderPath, myCheck(i), "00_Part Archive")
            Exit For
        End If
    Next i
    
'   If a match was found, populate txtArchivePath
    If myArchivePath <> "" Then
        Me.txtArchivePath = myArchivePath
    Else
        MsgBox "Folder Path cannot be found"
        Me.txtArchivePath = Null
    End If

[thescottsman92]

thank you

that has worked really well

cheers