Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
85 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 40

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 1000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 40

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
 

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 40

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 40

Accepted Solution

by:
PatHartman earned 1000 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

824 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