Trimming Hyperlink Path to PDF Documents to Just Document Name & displaying In Report

[MarcieFess]

I have PDF documents attached to records. Well, the hyperlinks to their location on the server is actually in the records.

I have a query that gets DISTINCT records for a particular store from user input in a form. The output looks like this:

\\WESERVER\Data2\Projects\Projects\Access DB Project\MSDS\ACETONE.pdf
\\WESERVER\Data2\Projects\Projects\Access DB Project\MSDS\ACNE EXFOLIATING PADS.pdf
\\WESERVER\Data2\Projects\Projects\Access DB Project\MSDS\Almond Oil.pdf
\\WESERVER\Data2\Projects\Projects\Access DB Project\MSDS\Bain De Terre Infinite Hold Spray.pdf
\\WESERVER\Data2\Projects\Projects\Access DB Project\MSDS\Batiste Dry Shampoo.pdf
\\WESERVER\Data2\Projects\Projects\Access DB Project\MSDS\Canola Oil.pdf
\\WESERVER\Data2\Projects\Projects\Access DB Project\MSDS\Coconut Oil.pdf
\\WESERVER\Data2\Projects\Projects\Access DB Project\MSDS\Coppertone Sunscreen Sprays.pdf

That's fine for the first report.

For a second report, I need to trim the first 58 characters from the hyperlink. I figured I'd use



Mid([qryMSDSPrint.MSDS] As String, 59 As Number) As String

to do that. Is that correct?

I want my final report to look like this:

Store # Store Name
MATERIAL SAFETY DATA SHEETS
TABLE OF CONTENTS
A

ACETONE.pdf
ACNE EXFOLIATING PADS.pdf
Almond Oil.pdf

next page

Store # Store Name
MATERIAL SAFETY DATA SHEETS
TABLE OF CONTENTS
B

Bain De Terre Infinite Hold Spray.pdf
Batiste Dry Shampoo.pdf


Next page


Store # Store Name
MATERIAL SAFETY DATA SHEETS
TABLE OF CONTENTS
C

Canola Oil.pdf
Coconut Oil.pdf
Coppertone Sunscreen Sprays.pdf


I already have the page header set up for everything except the letter of the alphabet for that page.

I imagine I would use the LEFT function to get the letter of the alphabet for each page.

My issue is, I am really, really green at reports. When it comes to limiting a report using VBA, functions, etc. I don't even know where to begin.

The end result of all of this will be that someone will manually print all of the PDFs. The procedure I'm working on now will print out a cover page for each letter of the alphabet, kind of like using a TOC but not really. It needs to be alphabetized by the name of each document.

If it helps, here's my code for the button so far:

'------------------------------------------------------------' btnMSDSSheetsPrint_Click
'
'------------------------------------------------------------
Private Sub btnMSDSSheetsPrint_Click()
On Error GoTo btnMSDSSheetsPrint_Click_Err




If IsNull(Me.cboCompany) Then
    MsgBox ("Choose a Company!")
    Me.cboCompany.SetFocus
    Exit Sub
End If


If IsNull(Me.cboStore) Then
    MsgBox ("Choose a Store!")
    Me.cboStore.SetFocus
    Exit Sub
End If


    DoCmd.OpenQuery "qryMSDSPrint"
        
    DoCmd.OpenReport ("rptMSDSTblOfCntnts")
    
      
    
ExitS:
    DoCmd.Echo True
    Exit Sub
    
btnMSDSSheetsPrint_Click_Exit:
    Exit Sub


btnMSDSSheetsPrint_Click_Err:
    If Err.Number = 2501 Then
  MsgBox ("No records exist for this store/company!")
Else
MsgBox Error$
End If
    Resume btnMSDSSheetsPrint_Click_Exit


End Sub

The line I put in red above is the line that will run the report that I'm trying to format right now.

Thanks, everyone, for your help. I truly appreciate it.

Marcie

[amrut]

You do not have to open the query to open the report based on it. You will need an additional field in the query Left("YourFileName",1) for alphabet list. Then in the report, you need to use "Group and Sort" (Design >Group & Sort) to group the results based on alphabet field and use the option to "keep whole group together on page" so that each alphabet starts on new page.This alphabet field should be in the "Group Header".

[June7]

The pdf file path is stored in a hyperlink type field? Don't need the string functions to break up a hyperlink. Review:
http://msdn.microsoft.com/en-us/libr.../ff844740.aspx

If the link is just a string in a text field, then use intrinsic string functions.
Mid([MSDS], 59)


Also of interest.
http://www.allenbrowne.com/casu-09.html
http://allenbrowne.com/func-GoHyperlink.html

[MarcieFess]

Thank you, Amrut. With a little tweaking, what you said worked for me!

One more question.

My query is:

SELECT DISTINCT Mid([Product.MSDS],59) AS MSDS, tblStoreInformation.StoreNum, tblStoreProducts.StoreKey, Left([MSDS],1) AS Alph
FROM tblStoreInformation INNER JOIN (Product INNER JOIN tblStoreProducts ON Product.[ProductKey] = tblStoreProducts.[ProductKey]) ON tblStoreInformation.StoreKey = tblStoreProducts.StoreKey

WHERE (((tblStoreProducts.MaxUnits)<>0) AND (([Product.HazardKey])<>79))

GROUP BY Mid([Product.MSDS],59), tblStoreInformation.StoreNum, tblStoreProducts.StoreKey, Left([MSDS],1)

HAVING (((Mid([Product.MSDS],59)) Is Not Null) AND ((tblStoreProducts.StoreKey)=[Forms]![frmChooseStore].[cboStore]));

So now, instead of returning

\\WESERVER\Data2\Projects\Projects\Access DB Project\MSDS\ACETONE.pdf

it's now returning

ACETONE.pdf#\\WESERVER\Data2\Projects\Projects\Acc ess DB Project\MSDS\ACETONE.pdf#

Why did it just take the beginning 58 letters and stick them on the end wrapped in # ?

I need it to actually truncate, not just move it around like that. I don't want to see anything between the '#'.

How do I do that?

If I solve this, the report is finished except for grouping one letter per page.

Marcie

[June7]

Did you read my post and review the links referenced?

[MarcieFess]

Did you read my post and review the links referenced?

Actually I did review the links. I'd posted that second question before your first response came through. It was perfect, thank you. The issue is solved!



Marcie