ACCESS FEED: Re: [MS_AccessPros] Replace function

Senin, 14 Desember 2015

Re: [MS_AccessPros] Replace function

John-

Thanks a lot.

Best Regards,

Kevin

发自我的小米手机

在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2015年12月14日下午6:40写道：

Kevin-

To be sure to get only the Hyperlink, you should do this:

Dim strTemp As String, intI As Integer

' Copy the text
strTemp = Me.Website
' Find the first #
intI = Instr(strTemp, "#")
' If no #, then do nothing
If intI = 0 Then Exit Sub
' Strip off everything up to and including the first #
strTemp = Mid(strTemp, intI + 1)
' Look for second #
intI = Instr(strTemp, "#")
' If none, then just copy
If intI = 0 Then
Me.Website = strTemp
Else
' Copy just up to the #
Me.Website = Left(strTemp, intI - 1)
End If

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Dec 14, 2015, at 11:08 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
The reason I want to remove this "#" is because that I changed the hyperlink to text type. I found that the hyperlink took long time to load. Thanks a lot.
Best Regards,
Kevin

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-12-14 17:57
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Replace function

Kevin-

MyString is just a variable you declared, and it will be empty when you call this code.

Why do you want to replace the # in the Website field? If it is defined as a Hyperlink data type, then it can contain up to four parts delimited with "#".

Display Text#Hyperlink address#Sub address#Tool tip

So, a Hyperlink might look like:

Recommended Reading#http://www.viescas.com/category/goodreads/##Click to see book recommendations

The second part is at the actual hyperlink, and if there was a bookmark on the page that the link needed to go to, it would be in the third part. When you look at this displayed in a Text Box, you will see "Recommended Reading" displayed. If you hover over the control, a tip will pop up displaying "Click to see book recommendations".

If you really want to replace the # characters, your code should be:

Me.Website = Replace(Me.Website, "#", " ")

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Dec 14, 2015, at 9:43 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Dear All,
I want to remove the "#" from the webaddress, I use Replace function. the code is like this:

Private Sub Command45_Click()
Dim MyString As String
MyString = Replace(Me.Website, "#", " ")
End Sub

After clicking the command button, nothing happened. What's wrong with the code? Thanks!
Best Regards,
Kevin