By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,948 Members | 1,929 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,948 IT Pros & Developers. It's quick & easy.

How to use InStr Function to get hyperlink display text?

P: n/a
I store hyperlinks as text in a table like this:
Invoice11-21-2003.pdf#file://P:\Finance\PrefVendors\Receipts\Invoice11-21-20
03.pdf

I need to run a report that lists the documents - or whatever the display
text of the hyperlink is.
As it is now, the entire hyperlink appears in the report - I'm trying to
display just the document name.

I've used this elsewhere to get the display text:

astrFile = Split(varFile, "\")
strDt = astrFile(UBound(astrFile))

But how to get this to work with the report?

The report has a query for a RecordSource, so I'm limited to using code
within the text box on the Report that is bound to the query. Is there a
way to use the IIf Function in the text box with the InStr Function? or
perhaps something like...

=IIf (astrFile = Split(varFile, "\"), strDt =
astrFile(UBound(astrFile)))

I've tried to use code in the Report's module, but that fails because the
textbox is bound to the query which gets the hyperlink text string from the
table.

any suggestion welcome...

Thanks!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
If you're using a newer version of Access (not ver 97) you can use
the InStrRev() function to get the last occurance. Otherwise you
have to loop.

strOut = Right([myField], Len([myField]) - InStrRev([myField],"\")

If you are using Access 97, post back and request the LastInStr()
function code. I'm not sure who wrote it, but it's been around forever.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"deko" <dj****@hotmail.com> wrote ...
I store hyperlinks as text in a table like this:
Invoice11-21-2003.pdf#file://P:\Finance\PrefVendors\Receipts\Invoice11-21-20
03.pdf

I need to run a report that lists the documents - or whatever the display
text of the hyperlink is.
As it is now, the entire hyperlink appears in the report - I'm trying to
display just the document name.

I've used this elsewhere to get the display text:

astrFile = Split(varFile, "\")
strDt = astrFile(UBound(astrFile))

But how to get this to work with the report?

The report has a query for a RecordSource, so I'm limited to using code
within the text box on the Report that is bound to the query. Is there a
way to use the IIf Function in the text box with the InStr Function? or
perhaps something like...

=IIf (astrFile = Split(varFile, "\"), strDt =
astrFile(UBound(astrFile)))

I've tried to use code in the Report's module, but that fails because the
textbox is bound to the query which gets the hyperlink text string from the
table.

any suggestion welcome...

Thanks!

Nov 12 '05 #2

P: n/a
I would use HyperlinkPart() to get either the address, display text, or both. I
could then use Instr() on the results of the HyperlinkPart() function

deko wrote:
I store hyperlinks as text in a table like this:

Invoice11-21-2003.pdf#file://P:\Finance\PrefVendors\Receipts\Invoice11-21-20
03.pdf

I need to run a report that lists the documents - or whatever the display
text of the hyperlink is.
As it is now, the entire hyperlink appears in the report - I'm trying to
display just the document name.

I've used this elsewhere to get the display text:

astrFile = Split(varFile, "\")
strDt = astrFile(UBound(astrFile))

But how to get this to work with the report?

The report has a query for a RecordSource, so I'm limited to using code
within the text box on the Report that is bound to the query. Is there a
way to use the IIf Function in the text box with the InStr Function? or
perhaps something like...

=IIf (astrFile = Split(varFile, "\"), strDt =
astrFile(UBound(astrFile)))

I've tried to use code in the Report's module, but that fails because the
textbox is bound to the query which gets the hyperlink text string from the
table.

any suggestion welcome...

Thanks!


Nov 12 '05 #3

P: n/a
Great. Thanks for the tip - HyperlinkPart() is the ticket!
"Salad" <hi**********@ranch.com> wrote in message
news:3F***************@ranch.com...
I would use HyperlinkPart() to get either the address, display text, or both. I could then use Instr() on the results of the HyperlinkPart() function

deko wrote:
I store hyperlinks as text in a table like this:

Invoice11-21-2003.pdf#file://P:\Finance\PrefVendors\Receipts\Invoice11-21-20 03.pdf

I need to run a report that lists the documents - or whatever the display text of the hyperlink is.
As it is now, the entire hyperlink appears in the report - I'm trying to
display just the document name.

I've used this elsewhere to get the display text:

astrFile = Split(varFile, "\")
strDt = astrFile(UBound(astrFile))

But how to get this to work with the report?

The report has a query for a RecordSource, so I'm limited to using code
within the text box on the Report that is bound to the query. Is there a way to use the IIf Function in the text box with the InStr Function? or
perhaps something like...

=IIf (astrFile = Split(varFile, "\"), strDt =
astrFile(UBound(astrFile)))

I've tried to use code in the Report's module, but that fails because the textbox is bound to the query which gets the hyperlink text string from the table.

any suggestion welcome...

Thanks!

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.