Keyword search is taking in the hyperlink (1 Viewer)

NikkiK1234

Member
Local time
Yesterday, 22:50
Joined
Jun 22, 2020
Messages
37
Hello again!

Unfortunately, I have more hyperlink issues with my form...

I have two records that use keyword searches to lookup certain names. However, I recently realized that the keyword search also includes parts of the hyperlink.

For example: A single field with a text called "Family" that is hyperlinked to a pdf on the computer. When I type in "pdf" or any part of the hyperlink, the "Family" field appears.

Is there a way to parse out the hyperlink for my two keyword searches? I want the hyperlink to work but I only want "Family" to appear only if the keyword(s) of "Family" is typed and not if any part of the hyperlink associated with the "Family" field is entered.

Thanks again
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:50
Joined
Oct 29, 2018
Messages
13,100
Hi. Not sure if this is what you mean, but for "parsing" the hyperlink field, you can try using the PlainText() function.
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:50
Joined
Mar 14, 2017
Messages
2,638
I'm a little confused. But if I am guessing correctly, it sounds to me like you have a tool (of some kind, not sure if it is code or...), which performs a Search. The Search ought to be being executed only on certain columns in the record. However, it is operating additionally on the column containing the Hyperlink, and it shouldn't do that.
Is that correct?

Can you please post the code that you use for searching?

While I do not use datatypes such as Hyperlinks (it's a treasured principle of mine to be very strict about keeping the Data and the Presentation layers separate and never mixing the two even conceptually, even if Microsoft allows it--I think this creates a better house in the long run)....if I am correct, we can't blame the Hyperlink data type (in this case :) ) It's just a matter of adjusting your Search tool to search in the correct columns
 

strive4peace

AWF VIP
Local time
Yesterday, 21:50
Joined
Apr 3, 2020
Messages
571
hi NikkiK1234

A Hyperlink is an object with 4 parts that are separated by #:
  • TextToDisplay
  • Address
  • SubAddress
  • ScreenTip
You could reference the part you're interested in
 

NikkiK1234

Member
Local time
Yesterday, 22:50
Joined
Jun 22, 2020
Messages
37
I'm a little confused. But if I am guessing correctly, it sounds to me like you have a tool (of some kind, not sure if it is code or...), which performs a Search. The Search ought to be being executed only on certain columns in the record. However, it is operating additionally on the column containing the Hyperlink, and it shouldn't do that.
Is that correct?

Can you please post the code that you use for searching?

While I do not use datatypes such as Hyperlinks (it's a treasured principle of mine to be very strict about keeping the Data and the Presentation layers separate and never mixing the two even conceptually, even if Microsoft allows it--I think this creates a better house in the long run)....if I am correct, we can't blame the Hyperlink data type (in this case :) ) It's just a matter of adjusting your Search tool to search in the correct columns

My search is performing on the right columns (with hyperlinks), but the keyword search is taking in the hyperlink address also but I don't want it to.
 

NikkiK1234

Member
Local time
Yesterday, 22:50
Joined
Jun 22, 2020
Messages
37
hi NikkiK1234

A Hyperlink is an object with 4 parts that are separated by #:
  • TextToDisplay
  • Address
  • SubAddress
  • ScreenTip
You could reference the part you're interested in

Code:
Private Sub ScaleText_Change()
    Dim myScale As String
    myScale = Nz(ScaleText.Text, "")
    Call myFilter("[ScaleName]", "ScaleText", myScale)
    currentrsrc = Me.tblTheme_Query_subform.Form.RecordSource
End Sub


Private Sub surveyText_Change()
    Dim mySurvey As String
    mySurvey = Nz(surveyText.Text, "")
    Call myFilter("[Survey]", "surveyText", mySurvey)
    currentrsrc = Me.tblTheme_Query_subform.Form.RecordSource
End Sub

So these are my classes that search the two columns for the hyperlink text. I'm assuming I add TextToDisplay, but where/how do I reference that part in my class?

Thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:50
Joined
Sep 21, 2011
Messages
7,109
As mentioned by theDBGuy, use the PalinText() Function on your field before passing it to the MyFilter function?
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:50
Joined
Jan 23, 2006
Messages
13,398
NikkiK1234,
Can you post a copy of your database with only enough records to highlight the issue?
 

strive4peace

AWF VIP
Local time
Yesterday, 21:50
Joined
Apr 3, 2020
Messages
571
hi NikkiK1234

try running this code on your hyperlink field, then look at the results in the Immediate window to see if you can find what you're looking for.

Change sTablename, sFieldname, and vWhere to your tablename, fieldname, and criteria if you have any

Rich (BB code):
Sub ShowPartsOfHyperlink()
'200807 strive4peace
' show all the parts of a hyperlink field using Application.HyperlinkPart
'
   '  CLICK HERE
   '  Press F5 to Run!
   '
   '  https://docs.microsoft.com/en-us/office/vba/api/access.achyperlinkpart
   '0  acDisplayedValue  The underlined text displayed in a hyperlink.
   '1  acDisplayText  The displaytext part of a Hyperlink field.
   '2  acAddress   The address part of a Hyperlink field.
   '3  acSubAddress   The subaddress part of a Hyperlink field.
   '4  acScreenTip The tooltip part of a Hyperlink field.
   '5  acFullAddress  The address and subaddress parts of a Hyperlink field, delimited by a "#" character.

   On Error GoTo Proc_Err

   Dim sTablename As String _
      ,sFieldname As String _
      ,vWhere As Variant

   Dim sSQL As String _
      ,i As Integer _
      ,iCount As Integer

   Dim db As DAO.Database _
      ,rs As DAO.Recordset
  
   '---------------------------'--------------------------- customize
   sTablename =  "Hyperlinx"
   sFieldname =  "Hyp"
   vWhere = Null  'Where clause without the word Where
   '---------------------------'---------------------------

   sSQL =  "SELECT a.[" & sFieldname &  "] as Hyp" _
      &  " FROM [" & sTablename &  "] a " _
      & ( " WHERE " + vWhere) _
      &  " ;"

   Set db = CurrentDb
   Set rs = db.OpenRecordset(sSQL,dbOpenDynaset)
     
   Debug.Print  "*** " & Now &  " document hyperlinks for: " & sTablename

   iCount = 0
   With rs
      Do While Not .EOF
         iCount = iCount + 1
         Debug.Print iCount; Tab(4); !Hyp
         For i = 0 To 5
            Debug.Print Tab(5); i; Tab(9); HyperlinkPart(!Hyp,i)
         Next i
         .MoveNext
      Loop
      .Close
   End With
   Set rs = Nothing

   MsgBox  "Done, press Ctrl-G to look at Debug window",, "LoopHyperlinx"

Proc_Exit:
   On Error Resume Next
   If Not rs Is Nothing Then
      rs.Close
      Set rs = Nothing
   End If
   Set db = Nothing
   Exit Sub

Proc_Err:
   MsgBox Err.Description,,_
        "ERROR " & Err.Number  _
        &  "  ShowPartsOfHyperlink"

   Resume Proc_Exit
   Resume

End Sub

' Made with free Color Code add-in posted on http://msaccessgurus.com/tool/Addin_ColorCode.htm
 
Last edited:

NikkiK1234

Member
Local time
Yesterday, 22:50
Joined
Jun 22, 2020
Messages
37
NikkiK1234,
Can you post a copy of your database with only enough records to highlight the issue?

Sure.

The keyword searches bring up the records that have the hyperlinks. Please use the first dropdown and select "CH" to see some examples. Thank you.
 

Attachments

  • V5.zip
    76.7 KB · Views: 28

strive4peace

AWF VIP
Local time
Yesterday, 21:50
Joined
Apr 3, 2020
Messages
571
@NikkiK1234,

if you just want to search the display text of your Survey hyperlink, then use this:
Rich (BB code):
 HyperlinkPart([Survey],1)

or ScaleName:
Rich (BB code):
 HyperlinkPart([ScaleName],1)

'0 acDisplayedValue The underlined text displayed in a hyperlink.
'1 acDisplayText The displaytext part of a Hyperlink field.
'2 acAddress The address part of a Hyperlink field.
'3 acSubAddress The subaddress part of a Hyperlink field.
'4 acScreenTip The tooltip part of a Hyperlink field.
'5 acFullAddress The address and subaddress parts of a Hyperlink field, delimited by a "#" character.
 
Last edited:

NikkiK1234

Member
Local time
Yesterday, 22:50
Joined
Jun 22, 2020
Messages
37
@NikkiK1234,

if you just want to search the display text of your Survey hyperlink, then use this:
Rich (BB code):
 HyperlinkPart([Survey],1)

or ScaleName:
Rich (BB code):
 HyperlinkPart([ScaleName],1)

'0 acDisplayedValue The underlined text displayed in a hyperlink.
'1 acDisplayText The displaytext part of a Hyperlink field.
'2 acAddress The address part of a Hyperlink field.
'3 acSubAddress The subaddress part of a Hyperlink field.
'4 acScreenTip The tooltip part of a Hyperlink field.
'5 acFullAddress The address and subaddress parts of a Hyperlink field, delimited by a "#" character.

So where do I add that code? Is it another function by itself? I tried adding it to my search private sub but I got a debugging error.

Thanks
 

strive4peace

AWF VIP
Local time
Yesterday, 21:50
Joined
Apr 3, 2020
Messages
571
hi @NikkiK1234

> " where do I add that code? "

terminology correction ... what I gave you is an EXPRESSION (not VBA code). You can use it in an SQL statement (query) or in the Control Source for a calculated control on a form or report.

To help with basics of Access, here is a good book for you to read that's short and free:

Access Basics
http://www.accessmvp.com/strive4peace
Free 100-page book that covers essentials in Access
 

NikkiK1234

Member
Local time
Yesterday, 22:50
Joined
Jun 22, 2020
Messages
37
CONTROLSOURCE.JPG


I added the expression in the control source of the form but I get an error "#NAME" message in the text box of the form that I inserted the expression in. Am I using the expression builder correctly?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:50
Joined
Oct 29, 2018
Messages
13,100
View attachment 86688

I added the expression in the control source of the form but I get an error "#NAME" message in the text box of the form that I inserted the expression in. Am I using the expression builder correctly?
Hi. Can't verify it myself right now, but did you type that in yourself, or did you pick everything from the builder?

Sent from phone...
 

NikkiK1234

Member
Local time
Yesterday, 22:50
Joined
Jun 22, 2020
Messages
37
Hi. Can't verify it myself right now, but did you type that in yourself, or did you pick everything from the builder?

Sent from phone...

I typed it in myself. I also tried this expression in a query:


Code:
SELECT tblTheme.Survey, HyperlinkPart([Survey],1) AS Expr1
FROM tblTheme;

It works but I need to find a way to incorporate it into my form.

Thanks again.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:50
Joined
Oct 29, 2018
Messages
13,100
I typed it in myself. I also tried this expression in a query:


Code:
SELECT tblTheme.Survey, HyperlinkPart([Survey],1) AS Expr1
FROM tblTheme;

It works but I need to find a way to incorporate it into my form.

Thanks again.
Can you post a copy of your form, so we can take a look?
 

NikkiK1234

Member
Local time
Yesterday, 22:50
Joined
Jun 22, 2020
Messages
37
Can you post a copy of your form, so we can take a look?
Sure. If you select "CH" from the first dropdown in form you will see two hyperlinked columns. I'm able to export those columns in an excel file but the hyperlinks remain; which I want to remove. I erased the expression in the screenshot I made above because it was causing me problems.

Thank you.
 

Attachments

  • Form.zip
    97.3 KB · Views: 5

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:50
Joined
Oct 29, 2018
Messages
13,100
Hi. Thanks! See if this does what you want.
 

Attachments

  • searchform.zip
    64.3 KB · Views: 3

Users who are viewing this thread

Top Bottom