UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Searching For Text With URL Text, Access 2013    
 
   
CCSlice
post Aug 4 2017, 02:50 AM
Post#1



Posts: 162
Joined: 12-August 07
From: Toronto, Canada


Hi UA

I am trying to create a function that would determine a website based upon a string found in the URL. At this time, there are only three websites I need to find, but that could expand. Right now I have a table called Websites that contain the text that I am searching:

tblWebsites
.gmail.com
.instagram.com
.facebook.com


then I have another table that has the usage data and a field called Site. Sorry, I cannot post the data because of the sensitive information contained within.

For example in tblUsage the field Site would look like:

app.facebook.com
app.gmail.com
app.fileshare.com.lu
app.instagram.com

How do I loop through the tblUsage data, or even, create a function in a query, so that when the function looks for a text within the field Sites, it will return the name of the site.

For example

SITE TYPE
=== ====
app.facebook.com FACEBOOK
app.instagram.com INSTAGRAM
app.gmail.com GMAIL


Thanks in advance, for your help with this!
Go to the top of the page
 
GroverParkGeorge
post Aug 4 2017, 07:44 AM
Post#2


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


There is probably more than one approach to doing this. Here's one idea.

In the table described, add a second field. Call it "ShortName" or something.

In this second field put the short name you want to associate with each web site. i.e. facebook.com and Facebook.

When you do a lookup against the website, you can use that ShortName field to return the value you want.

--------------------
Go to the top of the page
 
projecttoday
post Aug 4 2017, 10:58 AM
Post#3


UtterAccess VIP
Posts: 8,672
Joined: 10-February 04
From: South Charleston, WV


Am I correct? You just want the text between the first period and the second period?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
CCSlice
post Aug 4 2017, 05:49 PM
Post#4



Posts: 162
Joined: 12-August 07
From: Toronto, Canada


Hi Everyone,

Thank you for replying. What I am looking for is the text including the periods.
Go to the top of the page
 
CCSlice
post Aug 4 2017, 06:45 PM
Post#5



Posts: 162
Joined: 12-August 07
From: Toronto, Canada


So I wrote this code myself: It is a function that would determine the websites. I am just looking for three right now, but when that expands or becomes more dynamic, this code is not going to work.

CODE
Public Function GetWebApp (inpString as String) as String
Dim chckString1 as Integer, chckString2 as Integer, chckString3 as Integer
chckString1 = InStr (1, inpString, ".instagram.com"): chckString2 = InStr (1, inpString, ".facebook.com"): chckString3=InStr(1,inpString, ".gmail.com")

If chckString1 > 0 Then
    GetWebApp = "Instagram"
Else
        If chckString2 > 0 Then
           GetWebApp = "Facebook"
Else
               If chckString3 > 0 Then
                   GetWebApp = "Gmail"
Else
                  GetWebApp = "Indeterminate"
               End If
        End If
End if

End Function


And it works. The downside is that the websites are hard-coded but it will do for now. The challenge comes when the list expands or becomes dynamic.
Go to the top of the page
 
GroverParkGeorge
post Aug 5 2017, 07:05 AM
Post#6


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


There more than one possible approach you can take.

I think the heart of the solution involves the table-driven method I outlined. It will work. All you need to do to work with additional sites is add a new row.

The other part is going to be based on the question projecttoday asked.

If the value needed is ALWAYS a domain name, then parsing that will be fairly easy also.

However, it is important to know whether the website names will be consistent, i.e. that they all follow the same pattern: XXX.DOMAINNAME.COM

I suspect that, sooner or later, you will get other Top-Level Domains, like .org, or .biz, so your code must allow for that possibility as well: XXX.DOMAINNAME.YYY

Finally, you may, I would suspect, need to account for other cases where multiple parts are included: XXX.YYY.DOMAINNAME.ZZZ

Therefore, I'd do it like this:
CODE
Public Function ExtractDomainName(strDomainName As String) As String
    Dim strDomainNameParts() As String
    Dim i As Integer
    Dim DomainNameMatch As String

    ExtractDomainName = "Indeterminate"
    strDomainNameParts = Split(strDomainName, ".")
    For i = 0 To UBound(strDomainNameParts)
        DomainNameMatch = Nz(DLookup("DomainName", "tblDomainName", "tblDomainName.DomainName = """ & strDomainNameParts(i) & """"), vbNullString)
        If strDomainNameParts(i) = DomainNameMatch Then
            ExtractDomainName = strDomainNameParts(i)
            Exit For
        End If
    Next i

    Debug.Print ExtractDomainName
End Function


This requires a table of the domain names you expect to encounter. An even more generic method would be to select the domain name directly from the Array created by the Split() function. I rather expect someone to come up with that soon.

--------------------
Go to the top of the page
 
CCSlice
post Aug 5 2017, 07:58 PM
Post#7



Posts: 162
Joined: 12-August 07
From: Toronto, Canada


GPG,

This is a really nice solution. Thank you. I not familiar with the SPLIT function, so I am ignorant to its practicality in a situation like this. You're right: Later down the road there will be times in which I would need to extract website names from web data from file sharing apps. For example, Dropbox, has personal, as well as business accounts. Business accounts are further divided into sub-accounts in which the URL would read something like www.georgeraymond.companyname.######. Now getting the Dropbox with your method will work but there will be a need for insight into the companies using particular file sharing applications.

CCSlice

BTW GPG, love your book! Thanks for not writing/speaking in insurance! smile.gif
Go to the top of the page
 
GroverParkGeorge
post Aug 5 2017, 08:02 PM
Post#8


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


I'm glad to hear you liked it.

Split can be handy.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 07:57 PM