That code will fail if there is not a leading text#. This might be more robust
Dim strTemp As String
Dim pos1 As Long, pos2 As Long
strTemp = Me.WebSite
pos1 = InStr(strTemp, "#h")
pos2 = InStr(pos1 + 1, strTemp, "#")
pos2 = IIf(pos2 = 0, Len(strTemp) + 1, pos2)
Me.WebSite = Mid$(strTemp, pos1 + 1, pos2 - pos1 - 1)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 14 December 2015 10:40
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Replace function
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 <mailto:zhaoliqingoffice@163.com> ' zhaoliqingoffice@163.com <mailto:zhaoliqingoffice@163.com> [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto: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] <mailto:MS_Access_Professionals@yahoogroups.com>
Date: 2015-12-14 17:57
To: MS_Access_Professionals <mailto:MS_Access_Professionals@yahoogroups.com>
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 <mailto:zhaoliqingoffice@163.com> ' zhaoliqingoffice@163.com <mailto:zhaoliqingoffice@163.com> [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto: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
_____
[Non-text portions of this message have been removed]
Posted by: "Bob Phillips" <bob.phillips@dsl.pipex.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
Tidak ada komentar:
Posting Komentar