How to use InStr Function to get hyperlink display text?

[deko]

I store hyperlinks as text in a table like this:
Invoice11-21-2003.pdf#file://P:\Finance\Pref Vendors\Receipt s\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!

[Danny J. Lesandrini]

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*********@hot mail.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\Pref Vendors\Receipt s\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!

[Salad]

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\Pref Vendors\Receipt s\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!

[deko]

Great. Thanks for the tip - HyperlinkPart() is the ticket!
"Salad" <hi**********@r anch.com> wrote in message
news:3F******** *******@ranch.c om...

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\Pref Vendors\Receipt s\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!