469,266 Members | 1,768 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

Help using REPLACE in a query

Hi - I am a newbie to Access & SQL and I would appreciate some help on
this if at all possible.

I have a query that contains the following code.

SELECT DISTINCT Product.[Short description] AS [Product Name],
Product.[Full description] AS [Product Description] ;

The problem I have is that the Product.[Full description] contains
characters that I do not want displayed in the query but are required
in the database. Therefore I want to replace !!<<ul>, <li> and
</ul>>!! with nulls and replace </li> with a full stop (period) in the
query.

I believe that this cannot be done directly in access but requires a
function to be created.

Can anyone point me in the right direction?
Nov 12 '05 #1
8 19593
As I understand it, you can use REPLACE in a query in Access 2002 or 2003.

If you're using an earlier version, post back for more...
- Turtle
"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:qg********************************@4ax.com...
Hi - I am a newbie to Access & SQL and I would appreciate some help on
this if at all possible.

I have a query that contains the following code.

SELECT DISTINCT Product.[Short description] AS [Product Name],
Product.[Full description] AS [Product Description] ;

The problem I have is that the Product.[Full description] contains
characters that I do not want displayed in the query but are required
in the database. Therefore I want to replace !!<<ul>, <li> and
</ul>>!! with nulls and replace </li> with a full stop (period) in the
query.

I believe that this cannot be done directly in access but requires a
function to be created.

Can anyone point me in the right direction?

Nov 12 '05 #2
Hi - MS Access 2000 I'm afraid.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3
Yes, it does, but in earlier versions (i.e.: if all the service packs
haven't been installed), you can't use the VBA Replace function in a query
in Access 2000.

Fortunately, there's a trivial work-around.

Write your own shell to use the VBA Replace function, and use your shell in
the query, rather than Replace.

Function MyReplace(Expression As String, Find As String, Replace As String)
MyReplace = Replace(Expression, Find, Replace)
End Function

You then use MyReplace in the query.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:qf********************************@4ax.com...

Access 2K has the Replace Function as well.

On 26 Apr 2004 12:27:13 GMT, skinnybloke
<th**************@yahoo.co.uk> wrote:
Hi - MS Access 2000 I'm afraid.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #4
hi - thanks for the replies. I have got all of the service packs
installed so I can use replace - I tried this successfully for a single
string but I am totally clueless on how to do this for multiple strings
as per my original post.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5
rkc

"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:40*********************@news.frii.net...
hi - thanks for the replies. I have got all of the service packs
installed so I can use replace - I tried this successfully for a single
string but I am totally clueless on how to do this for multiple strings
as per my original post.

As Douglas J. Steele suggested and you quessed in your original post
you need to write your own function. Only in your case you need the
function to use the Replace function multiple times in one call.

Something like:

Function FixDescription ( s as string) as string
s = replace(s, "<li>", "")
s = replace(s, "<ul>", "")
s = replace(s, "<\ul>", "")
s = replace(s, "<\li>", ".")

FixDescription = s
End Function

Nov 12 '05 #6
skinnybloke <th**************@yahoo.co.uk> wrote in news:408e37ca$0$205
$7*******@news.frii.net:
hi - thanks for the replies. I have got all of the service packs
installed so I can use replace - I tried this successfully for a single
string but I am totally clueless on how to do this for multiple strings
as per my original post.


A quick scan of the web did not produce any solution for this, (but there
might be many good ones I overlooked).

So, with morning coffee, why not try to write one:

Her's my first try, tested on a whole two pieces of HTML.

Public Function StripHTMLTags( _
ByVal HTML As String) As String
Dim a() As String
Dim v As Variant
a() = Split(HTML, "<")
For Each v In a
StripHTMLTags = StripHTMLTags & Mid$(v, InStr(v, ">") + 1)
Next v
End Function

What's yours, or how would you improve mine?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #7
Hi - excellent stuff.

Just to take this one place further, how would this function need to
be modified to remove tags and the text between matching sets of html
tags.

e.g. <p>text text text</p>

remove the lot?

On 27 Apr 2004 11:16:41 GMT, Lyle Fairfield
<Mi************@Invalid.Com> wrote:
skinnybloke <th**************@yahoo.co.uk> wrote in news:408e37ca$0$205
$7*******@news.frii.net:
hi - thanks for the replies. I have got all of the service packs
installed so I can use replace - I tried this successfully for a single
string but I am totally clueless on how to do this for multiple strings
as per my original post.


A quick scan of the web did not produce any solution for this, (but there
might be many good ones I overlooked).

So, with morning coffee, why not try to write one:

Her's my first try, tested on a whole two pieces of HTML.

Public Function StripHTMLTags( _
ByVal HTML As String) As String
Dim a() As String
Dim v As Variant
a() = Split(HTML, "<")
For Each v In a
StripHTMLTags = StripHTMLTags & Mid$(v, InStr(v, ">") + 1)
Next v
End Function

What's yours, or how would you improve mine?


Nov 12 '05 #8
skinnybloke <th**************@yahoo.co.uk> wrote in
news:70********************************@4ax.com:
Hi - excellent stuff.

Just to take this one place further, how would this function need to
be modified to remove tags and the text between matching sets of html
tags.

e.g. <p>text text text</p>

remove the lot?


Probably, I would start considering the problem anew, without necessarily
basing any solution on the function in question.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by J Smith | last post: by
8 posts views Thread by skinnybloke | last post: by
3 posts views Thread by mailto.anand.hariharan | last post: by
6 posts views Thread by JackpipE | last post: by
13 posts views Thread by Jennifer.Berube | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.