Bob-
If it was a true Hyperlink data type, then the leading # will always be there. But I grant you the point. Note that your single test for #h won't find mailto and ftp links.
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 2:33 PM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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>
------------------------------------
------------------------------------
Yahoo Groups Links
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (10) |
Tidak ada komentar:
Posting Komentar