#http://www.bloomberg.com/quote/ABT:CI#

[access2010]

Could I receive assistance on removing both of the # signs from the beginning and ending copy?

My copy code is below
===
Private Sub K_WebSite_01_Click()
If Nz(Me.WebSite_01, "") <> "" Then
With Me.WebSite_01
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
DoCmd.RunCommand acCmdCopy
Else
MsgBox "There is no text for copying"
End If
End Sub

===
Your assistance is appreciated.
Paul

 

[pbaldy]

I'd just use the Replace() function on the .Value property of the textbox.

 

[access2010]

Thank you for your note.
Can you please show us the code that you would use as, I do not know where to place your suggested = Replace() = function.

Thank you,
Nicole

 

[pbaldy]

Replace(Me.WebSite_01, "#", "")

 

[access2010]

Thank you for your suggested code and you can see the results below.

=== Original code
If Nz(Me.WebSite_01, "") <> "" Then

=== Tried code
If Nz(Me.WebSite_01, "#") <> "" Then
Compile error

If Nz(Me.WebSite_01, "#", "") <> "" Then
Compile error

Do you have any other suggestion to remove the starting # sign and ending # sign in our Ms Access 2003 database

Thank you.
Paul

 

[pbaldy]

What I posted, used to do whatever you're trying to do:

If Nz(Me.WebSite_01, "") <> "" Then
  Whatever = Replace(Me.WebSite_01, "#", "") 
End If

Tested:

?Replace("#http://www.bloomberg.com/quote/ABT:CI#", "#", "")
http://www.bloomberg.com/quote/ABT:CI

 

[access2010]

Thank you for your assistance, and we have tried all the 3 different codes shown below without success.
=== Original ===
Private Sub K_WebSite_01_Click()
If Nz(Me.WebSite_01, "") <> "" Then
With Me.WebSite_01
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
DoCmd.RunCommand acCmdCopy
Else
MsgBox "There is no text for copying"
End If
End Sub
===
Private Sub K_WebSite_01_Click()
If Nz(Me.WebSite_01, "") <> "" Then
Me.WebSite_01 , "#", ""
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
DoCmd.RunCommand acCmdCopy
Else
MsgBox "There is no text for copying"
End If
End Sub
===
Private Sub K_WebSite_01_Click()
If Nz(Me.WebSite_01, "") <> "" Then
Replace(Me.WebSite_01, "#", "")
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
DoCmd.RunCommand acCmdCopy
Else
MsgBox "There is no text for copying"
End If
End Sub
===
If you could advise us what we have done wrong, I would appreciate it.

Nicole

 

[pbaldy]

None of those use what I suggested. What is the end goal?

 

[access2010]

At the present time when using Ms Access 2003, when the "K" copy command is clicked for a DATA TYPE "Hyperlink" field (web address) the string is copied with the "#" at the beginning and end of the copied data.
#https://www.barchart.com/stocks/quotes/NVDA#
===
When the "K" copy command is clicked for a "TEXT TYPE" field (stock code) the string is copied WITHOUT the "#" at the beginning and end of the copied data.
ABT
===
What would you suggest for the code to be used when copying the DATA TYPE "Hyperlink" field (web address)?
===
I/We do appreciate your suggestion.
Paul

 

[pbaldy]

So the end goal is to copy the contents to the clipboard? You can use the Replace() function to replace the contents of that textbox then run your process:

Me.WebSite_01 = Replace(Me.WebSite_01, "#", "") 
With Me.WebSite_01
  .SetFocus
  .SelStart = 0
  .SelLength = Len(.Text)
End With
DoCmd.RunCommand acCmdCopy

 

[access2010]

T H A N K Y O U

Your code works.

I/We appreciate your assistance.
Paul

Can I send you some snow and cold weather from Northern Canada.

 

[pbaldy]

Happy to help! I noticed the multiple names at the end of posts.

Snow yes, cold no.

 

[access2010]

I and other people volunteer at an NGO charity helping them with their endowment and other office work.

Paul

 

[access2010]

pbaldy, could you please advise me as to what I have done wrong in trying to copy the contents to the clipboard?
== Current Code ==
Private Sub Vendor_Website_Click()
If Nz(Me.Vendor_Contacts_VC_WebSite, "") <> "" Then
With Me.Vendor_Contacts_VC_WebSite
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
DoCmd.RunCommand acCmdCopy
'MsgBox "Text Copied into Memory"
Else
MsgBox "There is no text for copying"
End If
End Sub

== The code that I have tried that does not work ==
Me. Vendor_Contacts.VC_WebSite = Replace(Me. Vendor_Contacts.VC_WebSite, "#", "")
With Me.Vendor_Contacts_VC_WebSite
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
DoCmd.RunCommand acCmdCopy

===
I hope that you had an enjoyable Thanks Giving.
Paul

 

[pbaldy]

Well, I see you have the reference different. Good/bad:

Me.Vendor_Contacts_VC_WebSite
Me. Vendor_Contacts.VC_WebSite

Note space and dot instead of underscore. Haven't had dinner yet, enjoying family though! Hope you have/had one too, if you celebrate it.

 

[access2010]

Thank you for your note and I hope that you have had a pleasant and enjoyable Thanks Giving. Our Thanks Giving was October 08th
==
Could you please look at our attached database and tell us what we have done wrong with the (K) copy of the contents to the clipboard, so that the # sign is not added to the copied web field address.
==
Thank you,
Nicole

 

[Gasman]

There is no # in the control or the field in the table. ???

Even if there was, you have not included the Replace statement that was given to you.?

 

[access2010]

Thank you for your note.
When copying to the clip board from a Data Type Field = TEXT, we have no problem
When copying to the clip board from a Data Type Field = Hyperlink, every copy adds the # sign before and after the copied text.
=== We have tried the following code without success ===
Me. Vendor_Contacts.VC_WebSite = Replace(Me. Vendor_Contacts.VC_WebSite, "#", "")
With Me.Vendor_Contacts_VC_WebSite
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
DoCmd.RunCommand acCmdCopy
===
Your assistance in fixing our code will be appreciated.
Paul

 

[Gasman]

I will repeat. You have not included the Replace statement that was given to you

Your code

Private Sub Vendor_Website_Click()
' = = = K copy button
' = = = Source of data = Vendor_Contacts_VC_WebSite
If Nz(Me.Vendor_Contacts_VC_WebSite, "") <> "" Then
    With Me.Vendor_Contacts_VC_WebSite
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    DoCmd.RunCommand acCmdCopy
    'MsgBox "Text Copied into Memory"
Else
    MsgBox "There is no text for copying"
End If

End Sub

What has been suggested

Private Sub Vendor_Website_Click()
' = = = K copy button
' = = = Source of data = Vendor_Contacts_VC_WebSite
If Nz(Me.Vendor_Contacts_VC_WebSite, "") <> "" Then
[COLOR="Red"]    Me.Vendor_Contacts_VC_WebSite = Replace(Me.Vendor_Contacts_VC_WebSite, "#", "")[/COLOR]
    With Me.Vendor_Contacts_VC_WebSite
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    DoCmd.RunCommand acCmdCopy
    'MsgBox "Text Copied into Memory"
Else
    MsgBox "There is no text for copying"
End If

End Sub

When you use the latter code there are no # in the text.?

So I have just taken your DB and copied to clipboard using your button, and yes you get #.
I then added the code that you were given several posts ago and then no # is copied to the clipboard.?

 

[access2010]

Thank you for your assistance, but the web address is being doubled into the database and doubled into the clipboard. Yes there is no # sign
=== The code that we have tried is shown below===
Private Sub Vendor_Website_Click()
If Nz(Me.Vendor_Contacts_VC_WebSite, "") <> "" Then
Me.Vendor_Contacts_VC_WebSite = Replace(Me.Vendor_Contacts_VC_WebSite, "#", "")
With Me.Vendor_Contacts_VC_WebSite
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
DoCmd.RunCommand acCmdCopy
'MsgBox "Web Address has been Copied into the Clipboard"
Else
MsgBox "There is no Web Address for copying"
End If
End Sub
===
Please advise me as to what I have done that has duplicated the record as shown below
www.vistaprint.cahttp://www.vistaprint.ca
===
Thank you
Paul