UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Listbox - Display A Shortened Filename, Access 2013    
 
   
ajb_1976
post Jun 13 2019, 08:33 AM
Post#1



Posts: 2
Joined: 13-June 19



Hello,

I have successfully created a list box on a form that populates a with a list of files saved on a network drive and limits the results based on the selected user (all filenames are prefixed with usernames).

The final step would be to shorten the filepath, ideally so just the filename is displayed, not the full filepath. Is this possible?

Any help is greatly appreciated.

Many Thanks
Alan
Go to the top of the page
 
orange999
post Jun 13 2019, 08:42 AM
Post#2



Posts: 1,918
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Welcome Alan,

I'm sure it's possible, but we'll need some sample data in order to provide more focused response.

--------------------
Good luck with your project!
Go to the top of the page
 
ADezii
post Jun 13 2019, 09:14 AM
Post#3



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


This would be a typical scenario that you can go by.
  1. Sample Data (tblFilePaths):
    FIDFilePath
    1C:\WINDOWS\system32\en-US\icardie.dll.mui
    2C:\WINDOWS\system32\1033\sqlnclir10.rll
    5C:\MyDBs\Mapper\Mapper.accdb
    6C:\Pictures\Wedding\IMG_0035.JPG
    7K:\Education\2019\June\Graduates.docx
    8C:\Test\TesDB.mdb
    9C:\MyLog.txt
    10C:\WINDOWS\Downloaded Installations\BMP\{A9A9EAFE-569D-4F22-8013-ADDCCEF13A7E}\BACS.msi
  2. Query (qryFilePaths) that will be the RowSource of your List Box:
    SQL
    SELECT tblFilePaths.FID, tblFilePaths.FilePath, Mid$([FilePath],InStrRev([FilePath],"\")+1) AS FPath
    FROM tblFilePaths;
  3. Query OUTPUT after execution:
    FIDFilePathFPath
    1C:\WINDOWS\system32\en-US\icardie.dll.muiicardie.dll.mui
    2C:\WINDOWS\system32\1033\sqlnclir10.rllsqlnclir10.rll
    5C:\MyDBs\Mapper\Mapper.accdbMapper.accdb
    6C:\Pictures\Wedding\IMG_0035.JPGIMG_0035.JPG
    7K:\Education\2019\June\Graduates.docxGraduates.docx
    8C:\Test\TesDB.mdbTesDB.mdb
    9C:\MyLog.txtMyLog.txt
    10C:\WINDOWS\Downloaded Installations\BMP\{A9A9EAFE-569D-4F22-8013-ADDCCEF13A7E}\BACS.msiBACS.msi
  4. The RowSource of your ListBox will be set to qryFilePaths. I have the Bound Column = 2 (Absolute File Paths), and the Column Widths = 0";0";2" (only the Base FileName will be displayed).
  5. Hope this helps.




Go to the top of the page
 
ajb_1976
post Yesterday, 04:30 AM
Post#4



Posts: 2
Joined: 13-June 19



Thanks orange999 and ADezii.

I have solved this now by using the code at the following link (https://www.devhut.net/2010/09/19/ms-access-vba-import-directory-listing-into-a-table/) to insert my directory listing into a table and then using ADezii's select statement as a basis to split the string and am now just displaying the filename in my listbox.

Thanks again
Alan
Go to the top of the page
 
moke123
post Yesterday, 05:50 AM
Post#5



Posts: 1,357
Joined: 26-December 12
From: Berkshire Mtns.


Another option is to use a public function

CODE
Public Function fGetLastElement(strIN As Variant) As String

    Dim i As Integer

    strIN = Split(strIN, "\")

    i = UBound(strIN)

    fGetLastElement = Trim(strIN(i))

End Function


CODE
SELECT tblFilePaths.FID, tblFilePaths.FilePath,  fGetLastElement([FilePath]) AS FName FROM tblFilePaths;


hth
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th June 2019 - 10:08 PM