SQL problem

[neptune22]

hello everyone,

does anybpdy know of the equivalent string function of miscrosoft access 2003's "replace" function in its lower versions??

thanks in advance for any help you can give.

[Ecniv]

There wasn't one. It had to be coded by you.

Functions :
Instr, Left, Mid, Right


Vince

[neptune22]

Originally posted by Ecniv
There wasn't one. It had to be coded by you.

Functions :
Instr, Left, Mid, Right


Vince

is that so?
anyway thats for the info.
it's greatly appreciated.

[Garratt]

TempA: Original String
TempB: The search string ('BLUE') is inside of the TempA text
TempC: The String to replace the found string with if found (i.e. if "blue' is found, replace 'blue' with 'yellow'.
TempD: Will contain the new string with 'blue' turned into yellow.

Public Sub Example()
Dim TempA As String
Dim TempB As String
Dim TempC As String
Dim TempD As String

TempA = "asdfasdfBLUEasdfasd"
TempB = "BLUE"
TempC = "YELLOW"

TempD = Replace(TempA, TempB, TempC)


'.............  TempD will = "asdfasdYELLOWasdfasd"

End Sub
'-------------------------------------------------------------------

Public Function Replace(ByVal vntValue As Variant, ByVal strSource As String, ByVal strDest As Variant) As Variant
'Purpose    Replace part of a string by another.
'Inputs     vntValue    Value where execute substitution
'           strSource   String to replace
'           strDest     String replacement


Dim i, j As Integer
Dim intLngDest As Integer, intLngSource As Integer

    If IsNull(vntValue) Then
        Exit Function
    End If
    If strSource <> strDest Then
        intLngDest = Len(strDest)
        If intLngDest = 0 Then
            intLngDest = 1
        End If
        intLngSource = Len(strSource)
    
        i = 1
        j = InStr(i, vntValue, strSource)
        While j <> 0
            vntValue = Left(vntValue, j - 1) & strDest & Right(vntValue, Len(vntValue) - (j + intLngSource - 1))
            i = j + intLngDest
            j = InStr(i, vntValue, strSource)
        Wend
    End If
    Replace = vntValue
end function

[neptune22]

Originally posted by Garratt
TempA: Original String
TempB: The search string ('BLUE') is inside of the TempA text
TempC: The String to replace the found string with if found (i.e. if "blue' is found, replace 'blue' with 'yellow'.
TempD: Will contain the new string with 'blue' turned into yellow.

Public Sub Example()
Dim TempA As String
Dim TempB As String
Dim TempC As String
Dim TempD As String

TempA = "asdfasdfBLUEasdfasd"
TempB = "BLUE"
TempC = "YELLOW"

TempD = Replace(TempA, TempB, TempC)


'.............  TempD will = "asdfasdYELLOWasdfasd"

End Sub
'-------------------------------------------------------------------

Public Function Replace(ByVal vntValue As Variant, ByVal strSource As String, ByVal strDest As Variant) As Variant
'Purpose    Replace part of a string by another.
'Inputs     vntValue    Value where execute substitution
'           strSource   String to replace
'           strDest     String replacement


Dim i, j As Integer
Dim intLngDest As Integer, intLngSource As Integer

    If IsNull(vntValue) Then
        Exit Function
    End If
    If strSource <> strDest Then
        intLngDest = Len(strDest)
        If intLngDest = 0 Then
            intLngDest = 1
        End If
        intLngSource = Len(strSource)
    
        i = 1
        j = InStr(i, vntValue, strSource)
        While j <> 0
            vntValue = Left(vntValue, j - 1) & strDest & Right(vntValue, Len(vntValue) - (j + intLngSource - 1))
            i = j + intLngDest
            j = InStr(i, vntValue, strSource)
        Wend
    End If
    Replace = vntValue
end function

hey thanks for the reply Garratt..but what i really needed is a replace function inside a query statement..