#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Posts
    67
    Rep Power
    13

    Replace function in ADOQuery/JET 4.0


    Hi,

    I'm running a query from a Delphi app on data stored in an Access 97 *.mdb using ADOQuery that searches for data using LIKE . The problem is, I need to be able to search for words without typing in the spaces.

    For example - searching on '%alovelyday%'

    would retrieve not only "alovelyday", but also "a lovely day", or even "alove lyday"

    To do this I assumed I'd be able to use:


    Code:
    SELECT + FROM Table WHERE REPLACE(Column_Name, ' ', '') LIKE '%alovelyday%'

    But my JET 4.0 driver returns the following error:

    "undefined function 'replace()' in expression".

    So it looks like I can't use this function and will have to do it another way.

    So my question is.... can anyone think of another way of doing this? Can it be done with patternmatching? Is there a way of searching a string and telling the query to ignore whitespace?

    Please help - this has been driving me insane! It should be so simple ....!

    Comments on this post

    • Gran Roguismo agrees
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Central CT USA
    Posts
    2
    Rep Power
    0

    Replacement for "Replace" in Access 97


    This was posted on

    http://www.utteraccess.com/forums/sh...&Number=223288


    Code:
    Public Function fReplace(ByVal ValueIn As String, ByVal WhatToReplace As String, ByVal ReplaceValue As String) As String   
    
    Dim Temp As String
    Dim P As Long   
    
    Temp = ValueIn   
    P = InStr(Temp, WhatToReplace)   
        Do While P > 0      
            Temp = Left(Temp, P - 1) & ReplaceValue & Mid(Temp, P + Len(WhatToReplace))      
           P = InStr(P + Len(ReplaceValue), Temp, WhatToReplace, 1)   
        Loop   
    fReplace = Temp
    
    End Function

    Comments on this post

    • Gran Roguismo agrees

IMN logo majestic logo threadwatch logo seochat tools logo