Solved

MS Access Concatenate Where a Text String Is Found In Any Part Of A Field or File Name

Posted on 2016-10-19
10
41 Views
Last Modified: 2016-10-19
I am needing an equation to Concatenate all files with a specific text string in it with another field that is always the same. The file names can sometimes differ in name slightly if there is more then one file per product. But the file names will always have the SkuID (IE: VH51UN45JK0) in it somewhere.  Say I was needing all file paths with VH51UN45JK0 in the name. I would like to generate a Concatenate values like below.


Default Image Location File Path
C:\Users\Bob\Downloads\

File Image Names: VH51UN45JK0
VH51UN45JK0 (1).jpg
VH51UN45JK0 (2).jpg
VH51UN45JK0 (3).jpg
VH51UN45JK0 (4).jpg

Want to make this:

C:\Users\Bob\Downloads\VH51UN45JK0 (1).jpg
C:\Users\Bob\Downloads\VH51UN45JK0 (2).jpg
C:\Users\Bob\Downloads\VH51UN45JK0 (3).jpg
C:\Users\Bob\Downloads\VH51UN45JK0 (4).jpg


Thanks!
0
Comment
Question by:Dustin Stanley
  • 5
  • 4
10 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41851001
Use FSO (File System Object) to get a directory so you can see the file names and then use the Instr() function to find the filenames with the specified string.  If that isn't enough to get you started, I'll look for an example I have but I don't have it handy.
0
 
LVL 1

Assisted Solution

by:Vijay R
Vijay R earned 250 total points
ID: 41851009
Hi
Please try the following MS-DOS command in the command prompt
CD C:\Users\Bob\Downloads\
DIR *VH51UN45JK0* /B /S
Let me know if this does not works or need more information.
Thanks
Vijay R
0
 

Author Comment

by:Dustin Stanley
ID: 41851032
Hi
Please try the following MS-DOS command in the command prompt
CD C:\Users\Bob\Downloads\
DIR *VH51UN45JK0* /B /S
Let me know if this does not works or need more information.
Thanks
Vijay R

I don't think this will work but maybe there is something I don't know. Command prompt as in the PC command prompt. I need more like a query so I can use it in all the access Front End Applications on different computers.
0
 

Author Comment

by:Dustin Stanley
ID: 41851109
Use FSO (File System Object) to get a directory so you can see the file names

I am not familiar with this.
Does FSO give my Access Database a directory of all files from wherever I choose (IE Folder...)?

and then use the Instr() function to find the filenames with the specified string.

If I am correct about the above statement. Then will the Instr() function be like a find function in notepad or excel and find all the files with that specific string in it?

If that isn't enough to get you started, I'll look for an example I have but I don't have it handy.

Again if all is correct above please do so. This to me sounds very tricky but it sounds like what I need truly.

So once I find it I can Concatenate them together?

The end result I want is to be able to just drop these images into a folder and for a image control on a form to be able to display the images with the SKU in its file name and then associate the SKU with the forms current SKU. If that makes sense.

**Easiest thought but not as automated**
I think the easiest best idea here is just to browse and get file names individually. Place them in a new field (ImageNm) and Concatenate.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41851119
FSO will open a recordset that you would process in a read loop similar to a query of a table that you opened with DAO using rs.OpenRecordset.  As you loop through each entry, you would examine the file name to see if it contained the string.  So, it doesn't quite work like a query where you create a Where clause and get back only the rows that match.  In this case you get back all the files and it is up to you to find the files you want, one at a time.

Sorry, I can't provide the code right now but if you are somewhat familiar with VBA, you will be able to understand the examples you file.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Dustin Stanley
ID: 41851130
In this case you get back all the files and it is up to you to find the files you want, one at a time.

Not what I need.

query where you create a Where clause and get back only the rows that match.
Would it be possible to create a query with the where clause that could find matches with a partial filename in a external folder. Say right below the database in a folder called Images.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41851166
That isn't how FSO works.  Directories are not tables.  They are not under the control of Jet/ACE. They are under the control of DOS.  FSO instantiates a collection.  That collection includes a list of all the files in the directory along with their properties.  There are some methods to filter the list but you'll have to play with them.  I've only filtered by file extension.  So, I have code that will return all .docx files or all .pdf files.

Whether the collection contains all files of a directory and your code examines the names one at a time or if  the only files you get are the ones you want, the method still solves your problem.  The only difference is an If statement inside the loop to determine if you want to process the file or not.
0
 

Author Comment

by:Dustin Stanley
ID: 41851192
Thanks. I looked into it and so far I have this and I am tinkering around to see what I can get.

Private Sub Text1_Click()

On Error GoTo SubError
    'Add "Microsoft Office 14.0 Object Library" in references
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    
    
    Text1 = ""
   
    ' Set up the File Dialog)
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   
    With fDialog
        .Title = "Choose the Image you would like to import"
        .AllowMultiSelect = True
        .InitialFileName = "C:\Users\Shipper Station\OneDrive\Public\Photos\"  'Folder picker needs trailing slash
       
        .Filters.Clear
        .Filters.Add "Jpg", "*.Jpg*"
        .Filters.Add "Bmp", "*.Bmp"
        .Filters.Add "Png", "*.Png"
 
 
        If .Show = True Then
            If .SelectedItems.Count = 0 Then
                'User clicked open but didn't select a file
                GoTo SubExit
            End If
           
            'An option for MultiSelect = False
            'varFile = .SelectedItems(1)
            'txtSelectedName = varFile
           
            'Needed when MultiSelect = True
            For Each varFile In .SelectedItems
                Text1 = Text1 & varFile & vbCrLf
            Next
        Else
            'user cancelled dialog without choosing!
            'Do you need to react?
        End If
   
    End With
   
SubExit:
On Error Resume Next
    Set fDialog = Nothing
    Exit Sub
   
SubError:
    MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit
End Sub

Open in new window

0
 

Author Comment

by:Dustin Stanley
ID: 41851207
I am using this on a main form called frmSkusEntry and I am refrencing a subform called sbfrmProductImage. I am able to select image and then put the file path into the sbfrmProductImages field ImageURL.

But When I multiselect images there is only one line of New record in the sub form sbfrmProductImages. How can I get this to create multiple new records in sbfrmProductImages.

I believe this part has something to do with it.
Me!sbfrmProductImages.Form!ImageURL = Me!sbfrmProductImages.Form!ImageURL & varFile & vbCrLf

Open in new window



Private Sub Command119_Click()
If Me.SkuID & "" = "" Then
        MsgBox "Please enter SKU first.", vbInformation, "Warning"
       Me.SKU.SetFocus
       Else
    On Error GoTo SubError
    'Add "Microsoft Office 14.0 Object Library" in references
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    
    
    Me!sbfrmProductImages.Form!ImageURL = ""
   
    ' Set up the File Dialog)
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   
    With fDialog
        .Title = "Choose the Image you would like to import"
        .AllowMultiSelect = True
        .InitialFileName = "C:\Users\Shipper Station\OneDrive\Public\Photos\"  'Folder picker needs trailing slash
       
        .Filters.Clear
        .Filters.Add "Jpg", "*.Jpg*"
        .Filters.Add "Bmp", "*.Bmp"
        .Filters.Add "Png", "*.Png"
 
 
        If .Show = True Then
            If .SelectedItems.Count = 0 Then
                'User clicked open but didn't select a file
                GoTo SubExit
            End If
           
            'An option for MultiSelect = False
            'varFile = .SelectedItems(1)
            'txtSelectedName = varFile
           
            'Needed when MultiSelect = True
            For Each varFile In .SelectedItems
                Me!sbfrmProductImages.Form!ImageURL = Me!sbfrmProductImages.Form!ImageURL & varFile & vbCrLf
            Next
        Else
            'user cancelled dialog without choosing!
            'Do you need to react?
        End If
   
    End With
   
SubExit:
On Error Resume Next
    Set fDialog = Nothing
    Exit Sub
   
SubError:
    MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit
 End If
End Sub

Open in new window

0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41851228
If you select multiple files, you will need to loop through the selected collection and insert a separate row for each item selected.

I thought the code was doing the selection rather than an individual.  I would just have the user locate the directory and then have the code loop through and filter the files.  That way, the loop only has to handle one file at a time.

Giving the user a file selection dialog rather than a folder selection dialog, puts the burden on the user to select all the matching files.  As long as you know the string you are looking for, it is better to have the code do this than the user.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now