Opening pdf from a calculated field

[justgeig]

I have a calculated field in a query "Advertiser Lookup" that calculates the correct path to open a PDF that each ad was placed on. It works wonderfully and correctly displays each path for each ad (in text form). The problem is that I would like to allow my users to click a button in a form OR click the path within the query as a hyperlink. Either way would be fine and maybe it would be nice to have both options. In case anyone is wondering about what is in the calculated field, here it is.

PathToPDF: "R:\Peoples Back ups\" & Year(Year([Issue Date]) & " " & IIf(Month([Issue Date])<10,"0" & Month([Issue Date]),Month([Issue Date])) & " " & IIf(Day([Issue Date])<10,"0" & Day([Issue Date]),Day([Issue Date]))) & "\" & (Year([Issue Date]) & " " & IIf(Month([Issue Date])<10,"0" & Month([Issue Date]),Month([Issue Date])) & " " & IIf(Day([Issue Date])<10,"0" & Day([Issue Date]),Day([Issue Date]))) & " Folder\PDFs\" & (Year([Issue Date]) & " " & IIf(Month([Issue Date])<10,"0" & Month([Issue Date]),Month([Issue Date])) & " " & IIf(Day([Issue Date])<10,"0" & Day([Issue Date]),Day([Issue Date]))) & " " & [Page] & ".pdf"

and an example of the output is...

R:\Peoples Back ups\2010\2010 04 08 Folder\PDFs\2010 04 08 42.pdf

If I copy the output of the calculated field into Windows Explorer, it works perfectly. I even copied it into a test field that was set up as a hyperlink style and clicking it worked perfectly

Any suggestions as to how I can accomplish this? I'm not so hot with VBA so I bet that's what I need to do lol

[orange]

Don't have spaces in the filename. Use underscores and adjust your code accordingly.

[justgeig]

Hi orange:

thanks for the reply. The code is working great though and is generating the correct file path. I want to know how to make a button on a form use that file path to open the pdf.

[orange]

In the click event of your button you would have code

....
Application.FollowHyperlink yourCalculatedFileHere

[justgeig]

That did the trick :-) I can't believe how simple that was lol Thanks a bunch!

As for the query, is there a way to make a calculated field into a hyperlink so that it can be clicked from there as well?

[orange]

I'm not aware of a technique to do this from calculated field in a query. Normally, you allow the user to interact with the system via forms. So a button, properly named on a form, should do the trick.

[justgeig]

that's what I was afraid of, thank you so much for your help.