Replace function in ADOQuery/JET 4.0

Dev Shed Forums Sponsor:

November 17th, 2003, 03:02 AM

sochanik

Contributing User

Join Date: Aug 2001

Posts: 67

Time spent in forums: < 1 sec
Reputation Power: 12

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!

February 2nd, 2004, 02:04 PM

WS1O

Junior Member

Join Date: Oct 2003

Location: Central CT USA

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

Replacement for "Replace" in Access 97

This was posted on

URL

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!

Viewing: Dev Shed Forums > Programming Languages - More > Delphi Programming > Replace function in ADOQuery/JET 4.0

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump