Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    DDS is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    24

    Wildcard character with an exception (ie find anything except "R")

    I'm trying to find the most efficient way to return a list of file names in a folder that start with a particular string of text but ignore any that have the letter "R" directly after this particular string.
    This is an example of a situation:
    Folder contains these files (among many other files):


    18-001.jpg
    18-001 A.jpg
    18-001 B.jpg
    18-001R1.jpg
    18-001R2.jpg
    18-001R2 V2.jpg
    18-001R2 V3.jpg

    I'd like to find any files that start with "18-001" except the revisions of "18-001" (the revisions have an "R" straight after the "18-001").
    So in this scenario I'd be wanting to return only these file names:
    18-001.jpg
    18-001 A.jpg
    18-001 B.jpg

    My client uses the following file naming convention:
    Base design number: "YY-###.jpg" (eg. "18-001.jpg" would be the first design created in the year 2018)
    Revision of a design: "YY-###R#.jpg" (eg. "18-001R2.jpg" would be the second revision of design number 18-001. For all intents and purposes these revisions are treated as separate designs to their base design. The "R" is always placed directly after the base design number without any spaces.)
    Occasionally the client adds additional trailing identifiers to a design number. These vary and can be anything, (eg. "18-001 A.jpg", "18-001-A.jpg", 18-001 V2.jpg", "18-001 Rear View.jpg", etc).
    These are also sometimes added to design revisions too, (eg. "18-001R2 A.jpg", "18-001R2-A.jpg", 18-001R2 V2.jpg", "18-001R2 Rear View.jpg", etc). These may or may not have a space after the base design number but will never start with a capital "R" without a space first.

    Assuming I have correctly established the start of the file path string [FilePath].
    Lets say the value of my field [DesignNumber] is for example "18-001"
    At the moment I am establishing if there are any files which are based on design "18-001" (except for revisions) by doing something like this:
    Code:
    If Len(Dir([FilePath] & [DesignNumber] & ".*"))>0 Or Len(Dir([FilePath] & [DesignNumber] & " *"))>0 Then
    This works but it only captures either the base design number or files that have the design number with a space before the identifier. They may use dashes or underscores (or anything really) after the base design number and I want to make sure I capture any and all variations - I just want to avoid the revisions (trailing "R") as these are treated as a different design number.
    I have tried the following but it doesn't seem to work:
    Code:
    If Len(Dir([FilePath] & [DesignNumber] & "[!R]*"))>0 Then
    Could someone show me an efficient way to achieve this (refer to my opening sentence)?
    Sorry about the long winded explanation - thought some context might help.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    what about :
    not like "*R*"

  3. #3
    DDS is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    24
    Thanks Ranman, but without testing your suggestion, wouldn't that exclude any files that contain an "R" in the name? If so this would be problematic if the client named a file "18-001 Rear view.jpg" for example.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    So will R always be the seventh character?
    Code:
    if Mid(fieldname,7,1)<>"R" Then
    Tweak as necessary
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    assuming it starts with '18- then try

    Like "18-*" and not like "*R*"

    the ! wont work in this situation because the R can appear anywhere and [!R] is based on a specific location

    if the R only every appears at the 7th position then this will work

    Like "18-???[!R]*"

  6. #6
    DDS is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    24
    Yes, come to think of it I can't see why it wouldn't always be the seventh character. And to be doubly sure I suppose I could test if the 8th character is a number...
    Good idea and a viable solution. Thanks Colin. (I feel a bit silly now)
    Would be curious to know how to do an 'exception' to a wildcard character still for future reference if anyone reading this knows?

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Would be curious to know how to do an 'exception' to a wildcard character still for future reference if anyone reading this knows?
    see post #5

    ig you want to test the 8th character as well

    Like "18-???[!R][!R]*"

  8. #8
    DDS is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    24
    Thanks Ajax. No the "R" does always appear in the 7th position for revisions. I didn't seem to have any luck with [DesignNumber] & "[!R]*" where [DesignNumber] might be something like "18-001" which from what I can tell would be the same as your bottom line.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I didn't seem to have any luck with
    bit vague - what does it mean? returned wrong records, returned no records? didn't win on the horses?

    this works for me

    Like "18-001" & "[!R]*"

    so don't see why

    Like [DesignNumber] & "[!R]*"

    shouldn't work for you

    I would check your data - perhaps designnumber is not what you think?

  10. #10
    DDS is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    24
    Quote Originally Posted by Ajax View Post
    bit vague - what does it mean? returned wrong records, returned no records? didn't win on the horses?
    Means it did not return the results I would have expected from the code I created. Dummy test files with file names that I believe should have met the criteria were not found in the folder being inspected.

    However I was not using 'Like' in my code.

    Here is an abreviated version of my code for testing purposes:

    Code:
    Private Sub CountVariations_Click()
    
    
        Dim imgCount As Integer
        Dim imgPath As String
        Dim imgFile As String
        
        imgCount = 0
        imgPath = "C:\Users\Jon\Google Drive\OrionDDS\Artwork\3D\Still\"
    
    
        imgFile = Dir(imgPath & Me.DesignNumber & "[!R]*")
        Do While imgFile <> ""
            imgCount = imgCount + 1
            imgFile = Dir
        Loop
        
        MsgBox "Design Number " & Me.DesignNumber & Chr(13) & Chr(10) & imgCount & " variations found."
        
    End Sub
    The value of
    Code:
    Me.DesignNumber
    is "17-044"

    The folder being inspected contains these files:
    17-044.jpg
    17-044 A.jpg
    17-044 B.jpg
    17-044R1.jpg

    The result of the above code produces 0 results but I would have thought it should produce 3.

    By just changing
    Code:
    Dir(imgPath & Me.DesignNumber & "[!R]*")
    to
    Code:
    Dir(imgPath & Me.DesignNumber & " *")
    The result is what I would expect which is 2.

  11. #11
    DDS is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    24
    Based on Colin's suggestion the below code produces the desired result which is 3.
    Code:
    Private Sub CountVariations_Click()
    
    
        Dim imgCount As Integer
        Dim imgPath As String
        Dim imgFile As String
        
        imgCount = 0
        imgPath = "C:\Users\Jon\Google Drive\OrionDDS\Artwork\3D\Still\"
    
    
        imgFile = Dir(imgPath & Me.DesignNumber & "*")
        Do While imgFile <> ""
            If Not (Mid(imgFile, Len(Me.DesignNumber) + 1, 1) = "R" And IsNumeric(Mid(imgFile, Len(Me.DesignNumber) + 2, 1))) Then imgCount = imgCount + 1
            imgFile = Dir
        Loop
        
        MsgBox "Design Number " & Me.DesignNumber & Chr(13) & Chr(10) & imgCount & " variations found."
        
    End Sub
    I can work with this solution but if there's a way to stop the revision files from being included in the Dir() criteria in the first place I prefer less cluttered code.

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sorry hadn't realised you were using this in the Dir function. The Dir function only allows two wildcards "*" and "?". You'll need to use something like

    i
    Code:
    mgFile = Dir(imgPath & Me.DesignNumber & "*")
        Do While imgFile <> ""
            if imgfile like Me.DesignNumber & "[!R]* then imgCount = imgCount + 1
            ' or for better code clarity …. not(imgfile like Me.DesignNumber & "R*") ….
            imgFile = Dir
        Loop

  13. #13
    DDS is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    24
    Thanks Ajax.
    I've just discovered a bit of an issue with the above solutions for a different part of my project:
    Within my project there are 3 x types of subs/functions that use some sort of inspection of the folder for variations of the selected 'Design Number'.
    One just counts the variations (above solutions will suit this need)
    Another uses the returned design number variations as the RowSource value for a Combo Box who's Row Source Type is set to Value List (again above solutions will work)
    However my third need for the Dir() function is a little trickier:
    I want to set the Picture property of an image control to be the full path of the base design number plus the ".jpg" extension (eg. imgPath & Me.DesignNumber "17-044.jpg"). Alternatively, if this file does not exist then I want it to set this property to the first available file which has Me.DesignNumber as it's base (excluding revision "R" files of the design number).
    Below is the code I'm referring to:
    Code:
    Sub AssignStillImage()
    
    
        Dim stlFile As String
        
        If Len(Dir(Me.StillFile & ".*")) > 0 Then
            stlFile = Dir(Me.StillFile & ".*")
        Else
            stlFile = Dir(Me.StillFile & "[!R]*") '...This is where I'd like to find the first file which starts with the basic design number but is not a revision...
        End If
        
        If Len(stlFile) > 0 Then
             Me.StillImage.Picture = Me.StillPath & stlFile
        Else
             Me.StillImage.Picture = ""
        End If
        
    End Sub

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    As previously advised [!R] won't work with dir.

    It's not clear to me what you are trying to achieve if you can't adapt the code to use if i.e.


    Else
    imgfile=Dir(imgPath & Me.DesignNumber & "*")
    if imgfile not(like Me.DesignNumber & "R*") then stlFile= imgFile
    end if
    If the above does not meet your requirements, provide some examples - what is StillFile and an example value - does it include the path? does it include the design number?

  15. #15
    DDS is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    24
    Yes I did note that [!R] won't work with dir which was a very helpful revelation.
    I left it in the above code to help illustrate where I need the alternative solution.
    In that last code, the field StillFile contains the entire file path including the file name (which is the base design number) without any file extension.
    What I want to achieve is this:

    Let's say I have a folder containing the following files:

    17-044 A.jpg
    17-044 B.jpg
    17-044.jpg
    17-044R1.jpg

    Let's say the full path to this folder is called imgPath which is string. The imgPath value already includes the backslash at the end of it.
    Lets say I have a field called Me.DesignNumber which for this example contains the value "17-044" as text
    Let's say that imgFile is imgPath & Me.DesignNumber

    I want to assign the value of imgFile to the Picture property of the image control called Me.StillImage knowing that the file extension is more than likely going to be ".jpg" but not wanting to assume this.
    Now, even though the field Me.DesignNumber contains the value "17-044", there may not necessarily be a file in the imgPath folder with just that file name plus the file extension. It may contain only some variations of it such as "17-044-A.jpg" or "17-044 Side View.jpg" etc. So firstly, we need to evaluate whether a file with just the value of Me.DesignNumber plus a file extension exists. If it does this will be the image appearing in the Me.StillImage control. If such a file does not exist we need to look for any variations of this file to use as the image. We must remember however that any files starting with "17-044" but immediately follow with the letter "R" and then a number (eg. 17-044R2.jpg") are to be treated as an entirely different design number and so effectively design number "17-044R2" might as well be design number "17-123" with regards to comparing it to the "17-044" string.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-07-2016, 12:22 PM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 2
    Last Post: 01-18-2014, 08:42 PM
  4. Replies: 6
    Last Post: 06-07-2013, 09:45 AM
  5. Replies: 1
    Last Post: 10-08-2012, 09:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums