KeywordS (1 Viewer)

N1234

Member
Local time
Today, 12:42
Joined
Jun 22, 2020
Messages
64
Hello again!

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
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:42
Joined
Oct 29, 2018
Messages
21,358
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
Today, 09:42
Joined
Mar 14, 2017
Messages
8,738
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
Today, 11:42
Joined
Apr 3, 2020
Messages
1,003
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
 

N1234

Member
Local time
Today, 12:42
Joined
Jun 22, 2020
Messages
64
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:42
Joined
Sep 21, 2011
Messages
14,044
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
Today, 12:42
Joined
Jan 23, 2006
Messages
15,364
NikkiK1234,
Can you post a copy of your database with only enough records to highlight the issue?
 

strive4peace

AWF VIP
Local time
Today, 11:42
Joined
Apr 3, 2020
Messages
1,003
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:

N1234

Member
Local time
Today, 12:42
Joined
Jun 22, 2020
Messages
64
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.
 
Last edited:

strive4peace

AWF VIP
Local time
Today, 11:42
Joined
Apr 3, 2020
Messages
1,003
@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:

N1234

Member
Local time
Today, 12:42
Joined
Jun 22, 2020
Messages
64
@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.


Thanks
 
Last edited:

strive4peace

AWF VIP
Local time
Today, 11:42
Joined
Apr 3, 2020
Messages
1,003
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
 

N1234

Member
Local time
Today, 12:42
Joined
Jun 22, 2020
Messages
64
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?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:42
Joined
Oct 29, 2018
Messages
21,358
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...
 

N1234

Member
Local time
Today, 12:42
Joined
Jun 22, 2020
Messages
64
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:
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:42
Joined
Oct 29, 2018
Messages
21,358
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?
 

N1234

Member
Local time
Today, 12:42
Joined
Jun 22, 2020
Messages
64
Can you post a copy of your form, so we can take a look?
I erased the expression in the screenshot I made above because it was causing me problems.

Thank you.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:42
Joined
Oct 29, 2018
Messages
21,358
Hi. Thanks! See if this does what you want.
 

Attachments

  • searchform.zip
    64.3 KB · Views: 243

Users who are viewing this thread

Top Bottom