Could use some Query Assistance

[notarat]

Hi all!

I'm back to request some assistance about a query I am trying to do in MS Access.

Lagbolt and Jal (?) provided me with a great deal of assistance last year in http://www.access-programmers.co.uk/forums/showpost.php?p=784302&postcount=1

and now it seems I need some more assistance with a new query.

BACKGROUND
I have a file server our families use to store all the digital photos and movies, and other stuff for our rather large extended family. (we're talking about 20+families, comprised of about 110 people)

This results in a large amount of files and folders because I swear my family is positively addicted to cameras...(they need professional help, IMHO)

Using the database in the link I supplied, I can compile a complete listing of all folders and files and file size, path, owner, etc for the files on our family shared drives (about 450,000 pics and movies so far...told you they were crazy)

I have the full pathname to every file. in the PATHNAME field. It looks like

Z:\Thomas_Family\Weddings\Marissa\1\DSC004.JPG

I have also created an additional field in my database table to store the Name of the "Family" level folder for each family.

This is something new I've done in an effort to populate it with the name from the path. This would allow me to pull reports of the files and group them based on the family name, which I cannot do, currently.

I am just having no luck populating the FAMILYFOLDER field with the family name contained in the PATHNAME field.

Currently a list of pathnames looks like:

Z:\Thomas_Family\Weddings\Marissa\blah blah blah Z:\Thomas_Family\Weddings\Carrie\blah blah blah
Z:\Thomas_Family\Weddings\Joseph\blah blah blah
Z:\Zielinski_Family\Weddings\Cameron\blah blah blah
Z:\Zielinski_Family\Weddings\Tommy\blah blah blah
Z:\Zielinski_Family\Weddings\Scott\blah blah Z:\Messer_Family\Weddings\Delores\blah blah blah
Z:\Messer_Family\Weddings\James\blah blah blah
Z:\Messer_Family\Weddings\Clinton\blah blah blah

I created a query to remove the "Z:\" so the pathname now looks like:

Thomas_Family\Weddings\Marissa\blah blah blah Thomas_Family\Weddings\Carrie\blah blah blah
Thomas_Family\Weddings\Joseph\blah blah blah
Zielinski_Family\Weddings\Cameron\blah blah blah
Zielinski_Family\Weddings\Tommy\blah blah blah
Zielinski_Family\Weddings\Scott\blah blah Messer_Family\Weddings\Delores\blah blah blah
Messer_Family\Weddings\James\blah blah blah
Messer_Family\Weddings\Clinton\blah blah blah

What I want to do is create a query that populates the FAMILYFOLDER field with everything to the left of the first "\"

As I said before, this would let me pull reports of the files that I can group by family, which is something I cannot currently do.

I looked up the Left function, and it can pull information but I cannot get it to limit itself to everything to the left of the first "\"

Can I get some assistance please?

 

[namliam]

1)
Extracting the first folder out of the full path is what is considered to be a calculated value, you do not/should not store calculated values.

2)
To extract this information you use the Instr function and the mid function. These functions you use like:

mid("Z:\Zielinski_Family\Weddings\Cameron\blah blah blah", 4, instr(4,"Z:\Zielinski_Family\Weddings\Cameron\blah blah blah","\")-4)

Eliminating even the need to take away the leading Z:\, which this code does in one go.