Create an Alphabetized List & Truncate

[MarcieFess]

The project I am working on includes a field that is actually hyperlinks to PDF files in a folder on the server.

The hyperlinks all begin with

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

I've created the query that will provide the appropriate list of hyperlinks, and it's already alphabetized

This report is to serve as a table of contents of sorts. The client will be printing all of the PDFs for the particular store they are working on. They want this report to be able to print a list of the PDFs. One page per letter of the alphabet, with the letter of the alphabet at the top.

Basically I have query results that look 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


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'm assuming all of the header information needs to go into the Page Header section, not the Report Header section.

How do I:

1. Indicate the Letter of the Alphabet in the header
2. Truncate the path to the document, leaving only the document name?

I supposed it would be possible to list the actual product name instead of the PDF name, but the PDFs will be in the notebook by the actual name of the document.

Can someone please point me in the right direction?

Many thanks, as always, to the many helpful people on this forum!

Marcie

 

[namliam]

1) Kindoff depends on how you want things to look, but you can simply do "Left(filename,1)" to get the first char?
Alternatively make a table that holds all the chars a-z in seperate records, if you want to ensure the letters of the alphabet are there even if there is no content, then join to your filenames based on "Left(Filename,1)"

2) Use the InstrRev function to find the last "\" then use the mid or right function to "cut off" only the file name

Hope this helps you get started.

 

[MarcieFess]

Mailman,

Thank you so much for the tips.

My next question...am I doing this in the report, or in a query?

I'm relatively new at this, have just recently gotten very comfortable creating all kinds of queries, but I still struggle quite a bit with reports.

Right now, the button on the form runs the query and shows them the complete list of all the PDFs associated with that store. Since I know of no way to pass the "print" command through to Adobe to print every PDF in the list, they know they are going to have to have someone go down the list manually to print them. They're fine with that.

On the button, after the query, I'm having the TOC report open. I've created it with no report header, but the Store # & Store Name and the report title are all in the Page Header.

The alphabet letter will go there, too

There's 1 field in the entire report: MSDS

Do I change the field to be something else? Do I change the data source for the field?

This is where I get confused. I've tried a few different things and so far nothing is working.

Thanks for your help!

Marcie