Splitting a hyperlink into two fields

  • Thread starter Thread starter speaton via AccessMonster.com
  • Start date Start date
S

speaton via AccessMonster.com

Hello, everyone.

I have a database that has been in use for about a year and has around 2000
records in its main table, "tblThreads". In this table, we had been using a
field called "Title" to store the titles of various online discussion threads
as hyperlinks in the typical Access style:

Display Text#URL#

For a number of reasons, this year we want to split "Title" into two fields,
"Title" (containing the original display text) and "URL" (containing the
original URL as a hyperlink). Can anyone point me to a quick way to do this?

With many thanks in advance for any pointers,

Patrick
 
Use an Update query (Update on query menu, in query design.)

For the field that will contain the display text use:
HyperlinkPart([Title], 1)

For the address:
HyperlinkPart([Title], 2)
 
Allen said:
Use an Update query (Update on query menu, in query design.)

For the field that will contain the display text use:
HyperlinkPart([Title], 1)

For the address:
HyperlinkPart([Title], 2)
Click to expand...

Thank you very much, Allen!

That worked perfectly. I don't know why I was thinking something more
difficult would be required.

If I may be so bold as to follow that up with another question, I would now
like to be able to produce a list of the thread counts for each site, for
which I would like to use the part of the URL that comes after "http://" and
before the third "/" character (in the case of this site, for example, the "
www.accessmonster.com" part). Do you have any recommendations on how I might
go about getting something like that?

Thanks again for the help. I greatly appreciate it!

Best regards,

Patrick Eaton
 
Allen said:
Use Instr() to locate the first "/", starting with the 8th character.
Click to expand...

Excellent. Thank you once again, Allen. These pointers are tremendously
helpful.

A bit of browsing around the Visual Basic for Applications Reference material
and some subsequent experimentation suggest that what I want is something
like this:

---

SELECT Mid(,8,(InStr(8,[URL],"/",1)-8)) AS Site
FROM tblThreads;

---

If you know of a better way to go about it, I would certainly be glad to hear
it, but for now it looks like this will produce what I need.

With many thanks for pointing me in the right direction,

Patrick Eaton
 
On the off chance that your URL doesn't have the 3rd "/", you might want to
change it to:

SELECT Mid(
Allen said:
Use Instr() to locate the first "/", starting with the 8th character.
Click to expand...

Excellent. Thank you once again, Allen. These pointers are tremendously
helpful.

A bit of browsing around the Visual Basic for Applications Reference material
and some subsequent experimentation suggest that what I want is something
like this:

---

SELECT Mid([URL],8,(InStr(8,[URL],"/",1)-8)) AS Site
FROM tblThreads;

---

If you know of a better way to go about it, I would certainly be glad to hear
it, but for now it looks like this will produce what I need.

With many thanks for pointing me in the right direction,

Patrick Eaton
[/QUOTE]"] & "/",8,(InStr(8,[URL] & "/","/",1)-8)) AS Site
FROM tblThreads;

This just adds a "/" to the end of the URL for both the MID( ) and InSTR( )
functions. If the 3rd "/" is missing, the INSTR( ) function will return a -8
for a length, which we both know is not possible.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



speaton via AccessMonster.com said:
Allen said:
Use Instr() to locate the first "/", starting with the 8th character.
Click to expand...

Excellent. Thank you once again, Allen. These pointers are tremendously
helpful.

A bit of browsing around the Visual Basic for Applications Reference material
and some subsequent experimentation suggest that what I want is something
like this:

---

SELECT Mid([URL],8,(InStr(8,[URL],"/",1)-8)) AS Site
FROM tblThreads;

---

If you know of a better way to go about it, I would certainly be glad to hear
it, but for now it looks like this will produce what I need.

With many thanks for pointing me in the right direction,

Patrick Eaton
Click to expand...
 
Allen, Dale,

Thank you both for the feedback. Some comments follow.

Dale said:
On the off chance that your URL doesn't have the 3rd "/", you might want to
change it to:

SELECT Mid( & "/",8,(InStr(8,[URL] & "/","/",1)-8)) AS Site
FROM tblThreads;
Click to expand...



That's a good point, and certainly one that I had not considered, but I'm
reasonably sure that all of the URLs will have a third "/" because we are
linking to individual discussion threads, not just to the top-level domain
where the site can be found. Still, if we encounter this problem at any point,
I will be sure to keep your suggestion in mind. Thank you!

One other thing that I noticed is that although the initial query is giving
me the results I had hoped for, I don't seem to be able to expand it to
generate a thread count for each site, as I had hoped. When I try the
following query, for example, I get an error about the procedure being
incorrect:

---

SELECT Mid([URL],8,(InStr(8,[URL],"/",1)-8)) AS Site, Count(tblThreads.
ThreadID) AS ThreadCount
FROM tblThreads
GROUP BY Mid([URL],8,(InStr(8,[URL],"/",1)-8))
ORDER BY Count(tblThreads.ThreadID) DESC;

---

Because I don't need to generate a count of threads by site very often, I
figure I can easily work around this by creating a separate "Site" field in
tblThreads, run an update query with the original query to get all the sites,
and then just run a new query to get a count of the threads by Site whenever
necessary, but it would certainly be more convenient to be able to do this in
a single query. I'm now looking for ways to do that, but if either of you
have any suggestions, I would be very happy to hear them.

With many thanks,

Patrick Eaton[/QUOTE]
 
Back
Top