Displaying a hyperlinked image

Hi all,

I have a database which creates hyperlinks to a network folder full of images (too many to attach to the database) but I can't figure out an easy way to show those images in forms and reports.

I think I'm 90% there, looking online I figured out that if i create a text field (not a hyperlink) in the original table and copy over the hyperlink text, I can use this as the control source of an image control (with the picture type set to 'linked').

That works fine, but I would still have to manually copy over the URL every time I link to a new picture - so is there a way i can get this to feed through automatically?

I've tried a couple of ways already (calculated fields, 'set value' macros) but the problem is anytime I try to get clever, Access doubles the link address, making it utterly useless as a control source.

So for example, i ran a macro to set the text field value to equal the hyperlink value of c:\mydocs\picture.jpg,
and the result looked like this c:\mydocs\picture.jpg#c:\mydocs\picture.jpg#

Useless.

So any ideas how to prevent this? Or maybe there are functions similar to excel (like 'left' and 'len') which i can use to extract the right part of the hyperlink.

Please help

Thanks,

Try Mid (http://www.techonthenet.com/access/functions/string/mid.php) function.. To get the break using the InStr (http://www.techonthenet.com/access/functions/string/instr.php)..

Paul you are my savior once again, it worked perfectly thank you very much

For the benefit of anyone else in this situation, here's my solution:
- create a calculated field in the table with the hyperlink address (called evidence in my case)
- set the calculated field to this, using Paul's suggestions: =Mid([Evidence],1,InStr(1,[Evidence],"#")-1)
- create an image in the form and set its control source to the calculated field, then set the images picture type (format tab) to linked. and unless I've missed anything that should do it!

Thanks again

I am glad to help ! although I have a small (important IMHO) suggestion. Do not create a Calculated field.

Create an Unbound control on the Form, does not need to be visible, in its Control Souce, use the same Code. Then set the Image Control's Control Source to this Text Box.

Or even in the Form's Recordsource, you can calculate the value at Runtime/Dynamically. Using the same code. Add this as another column to the Recordsource.

I was going to ask why but then I found this: http://allenbrowne.com/casu-14.html

I will move the calculation.

thanks again!

You found your own answer Kudos !

Holy cow. Just when you thought Access couldn't get any goofier...calculated fields in tables?!?