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?

re: Help using REPLACE in a query

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" <theoriginalsin73@yahoo.co.uk> wrote in message
news:qgrp80tc3jgrufobjat7gt99pvkg2euf3f@4ax.com...[color=blue]
> 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?[/color]

re: Help using REPLACE in a query

Hi - MS Access 2000 I'm afraid.



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

re: Help using REPLACE in a query

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.grimsby@worldnet.att.net.invalid> wrote in message
news:qf3r80hn439jhnd10191frfkftg49h1v5n@4ax.com...[color=blue]
>
> Access 2K has the Replace Function as well.
>
> On 26 Apr 2004 12:27:13 GMT, skinnybloke
> <theoriginalsin73@yahoo.co.uk> wrote:
>[color=green]
> >Hi - MS Access 2000 I'm afraid.
> >
> >
> >
> >*** Sent via Developersdex http://www.developersdex.com ***
> >Don't just participate in USENET...get rewarded for it![/color]
>[/color]

re: Help using REPLACE in a query

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!

re: Help using REPLACE in a query

"skinnybloke" <theoriginalsin73@yahoo.co.uk> wrote in message
news:408e37ca$0$205$75868355@news.frii.net...[color=blue]
> 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.[/color]


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

re: Help using REPLACE in a query

skinnybloke <theoriginalsin73@yahoo.co.uk> wrote in news:408e37ca$0$205
$75868355@news.frii.net:
[color=blue]
> 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.[/color]

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)

re: Help using REPLACE in a query

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
<MissingAddress@Invalid.Com> wrote:
[color=blue]
>skinnybloke <theoriginalsin73@yahoo.co.uk> wrote in news:408e37ca$0$205
>$75868355@news.frii.net:
>[color=green]
>> 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.[/color]
>
>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?[/color]

re: Help using REPLACE in a query

skinnybloke <theoriginalsin73@yahoo.co.uk> wrote in
news:70ls80ts6tajbvck2q49jg3619cd23gnll@4ax.com:
[color=blue]
> 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?[/color]

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)