Parsing Function for Folder pathway

MS Access 2016 64 bit Windows 10 Home Edition

Need a function to parse the folder number from a string.

Strings look like:

C:\Users\folder a\folder b\date\1\
C:\Users\folder a\folder b\date\19\
C:\Users\folder a\folder b\date\196\

The part up to the number folder is highly variable and so that is why I do not list specific folder names as the counts would change.

Is there a function that could strip the \ out and return the number portion only?
Bill NelsonITAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
Then it's

Option Compare Database
Option Explicit

Public Function fGetNumber3(ByVal AValue As Variant) As Variant
' Return found Number or NULL
' Using Martin Liss's get last path approach.

  On Local Error GoTo LocalError

  Dim LastPart As String
  Dim Path() As String
  Dim Result As Long

  fGetNumber3 = Null
  If Len(Trim(AValue & "")) > 0 Then
    Path() = Split(CStr(AValue), "\")
    LastPart = Path(UBound(Path()) - 1)
    If IsNumeric(LastPart) Then
      Result = CLng(LastPart)
      If Result > 0 And Result < 100000 And CStr(Result) = LastPart Then
        fGetNumber3 = Result
      End If
    End If
  End If

  Exit Function
  
LocalError:
  fGetNumber3 = Null
  Debug.Print "Error: " & Err.Number & " - " & Err.Description
  On Local Error Resume Next
  Debug.Print "For Value: [" & CStr(AValue) & "]"
  
End Function

Open in new window


with
Capture.PNG
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try this User Defined Function...

Function GetNumber(ByVal Str As String) As Long
Dim RE As Object, Match As Object, Matches As Object
Set RE = CreateObject("VBScript.RegExp")
With RE
   .Global = False
   .Pattern = "\d+"
End With
If RE.test(Str) Then
   Set Matches = RE.Execute(Str)
   GetNumber = Matches(0)
End If
End Function

Open in new window

And then try this as...
GetNumber(<your string here>)

Open in new window

0
 
Rgonzo1971Commented:
Hi,

pls try (my code only takes the number at the end of the path with or without \
Function fGetNumber(ByVal Str As String) As Long
Dim RE As Object, Match As Object, Matches As Object
Set RE = CreateObject("VBScript.RegExp")
With RE
   .Global = False
   .Pattern = "(\d+)(\\)?$"
End With
If RE.test(Str) Then
   Set Matches = RE.Execute(Str)
   fGetNumber = Matches(0).submatches(0)
End If
End Function

Open in new window

REgards
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Martin LissOlder than dirtCommented:
Or...

Function GetNumber(ByVal Str As String) As Long
Dim strParts() As String

strParts = Split(Str, "\")
GetNumber = strParts(UBound(strParts) - 1)
End Function

Open in new window

2
 
Bill NelsonITAuthor Commented:
None of the proposed functions are working.
I am calling the function in the query grid in the criteria area, in the field FPath, and have tried:

GetNumber([FPath])
abd the other function names proposed as well. No results are returned....just blank
0
 
PatHartmanCommented:
The functions may not be working because you have been less than precise in your description of the format.  Try posting a few ACTUAL strings.
0
 
Bill NelsonITAuthor Commented:
F:\2018 To Be Data Entered\1  JAN  2018  Data Entry 1\04-01-2018\1\
F:\2017 To Be Data Entered\6  JUNE  DATA ENTRY\2017-06-01\20\
D:\2017 To Be Data Entered\3  MARCH  DATA ENTRY\2017-03-31\151\

Do you need more than three?
0
 
Martin LissOlder than dirtCommented:
I'm not an Access expert by any means, but when you say that GetNumber([FPath]) doesn't work, did you get an error message? Did you try GetNumber(rs!FPath), where rs is the name of your recordset, or GetNumber(FPath)?
0
 
aikimarkCommented:
The Neeraj pattern should be: \\(\d+)\\    <- the desired numbers are "\" delimited
The Rgonzo pattern should be: (\d+)\\?$   <- only need one capture group

@Bill Nelson,
Will the numbered directory always be the last directory in the path?
0
 
Bill NelsonITAuthor Commented:
Hi Martin,

No error is generated, the query just does not return any records...

I tried your suggestion, however, it is asking for the parameter to be entered...
0
 
Bill NelsonITAuthor Commented:
Yes, Akimark, it will always be the last directory in the path. The number of that directory can be from 1 digit to three digits long...

I will try your suggested edits and report back asap.

Thanks very much !
0
 
Martin LissOlder than dirtCommented:
Try putting the value of your database field in a variable, and then use the variable. Something like

Dim MyVar As String

MyVar = ...

GetNumber(MyVar)
0
 
aikimarkCommented:
And the Neeraj code needs to read:
GetNumber = Matches(0).Submatches(0)

Open in new window

0
 
Bill NelsonITAuthor Commented:
I get Data Type Mismatch, Run time error 13 using this in the query grid criteria "GetNumber([FPath])"

This is the code in the module:

Option Compare Database
Option Explicit

Public Function GetNumber(ByVal Str As String) As Long
Dim RE As Object, Match As Object, Matches As Object
Set RE = CreateObject("VBScript.RegExp")
With RE
    .Global = False
    .Pattern = "\\(\d+)\\"
End With
If RE.test(Str) Then
    Set Matches = RE.Execute(Str)
    GetNumber = Matches(0)
    End If
End Function

Open in new window

0
 
aikimarkCommented:
Please read my prior comment
0
 
Bill NelsonITAuthor Commented:
Hi Martin,

I get a data type mismatch error with your latest suggestion:

Option Compare Database
Option Explicit

Function fGetNumber3(ByVal Str As String) As Long
Dim strParts() As String

strParts = Split(Str, "\")
fGetNumber3 = strParts(UBound(strParts) - 1)
Debug.Print
End Function

Open in new window

0
 
Bill NelsonITAuthor Commented:
Hi Aikimark,

I added the additional information but no records are returned.

Option Compare Database
Option Explicit

Public Function GetNumber(ByVal Str As String) As Long
Dim RE As Object, Match As Object, Matches As Object
Set RE = CreateObject("VBScript.RegExp")
With RE
    .Global = False
    .Pattern = "\\(\d+)\\"
End With
If RE.test(Str) Then
    Set Matches = RE.Execute(Str)
    GetNumber = Matches(0).Submatches(0)
    End If
End Function

Open in new window


I am calling this module in the query grid as "fGetNumber3([FPath])"
0
 
Martin LissOlder than dirtCommented:
What value is in FPath? To find out you can put a breakpoint on the "fGetNumber3([FPath])" line, or by placing a MsgBox that displays it before that line.
0
 
Bill NelsonITAuthor Commented:
Hi Aikimark,

On Debug:
fGetNumber3=0
 UBound(strParts)="30-01-18"
UBoundStrParts=6

Is this why I am getting the Runtime error 13? Type Mismatch error... it is comparing a date to the number 6?
0
 
ste5anSenior DeveloperCommented:
For functions used in queries you should use a different signature:

Public Function GetNumber(ByVal AValue As Variant) As Long

Open in new window


And test the input before and do not rely on implicit conversion.
0
 
Bill NelsonITAuthor Commented:
Hi Martin,

Did you see my comment to Aikimark for the Debug values?

I am not sure how to do the msg box... nowhere in the code does it contain [FPath]... just in the query grid. Sorry, I am not experienced with this..
0
 
Bill NelsonITAuthor Commented:
Hi Ste5an,

We have had so many variations I am getting lost... could you please show me how the function should look and how I would go about testing the input before conversion? I am really new to VBA...
0
 
ste5anSenior DeveloperCommented:
Post (attach) a sample database showing your problems..
0
 
Bill NelsonITAuthor Commented:
Ok will do, just need to clear out some information first. Thanks so much for your help.
0
 
Martin LissOlder than dirtCommented:
I am not sure how to do the msg box... nowhere in the code does it contain [FPath]... just in the query grid. Sorry, I am not experienced with this..
And since I don't know Access very well I'm just confusing things, so I'll drop out of the conversation.
0
 
Bill NelsonITAuthor Commented:
Here is the attached database. I need to extract the folder number at the end of a string as that value is required in several different places.

The query "q_FPath_No_Z" is where I am trying to extract that folder number and name it "LastFolderIs"

Thank you for your help !
0
 
Bill NelsonITAuthor Commented:
Hi Martin,

Thanks for trying to help... I sincerely appreciate it !
0
 
Bill NelsonITAuthor Commented:
Here is the database... I forgot to attach it.
EE-Folder-Number-v-1.zip
0
 
ste5anSenior DeveloperCommented:
Okay, the function is to parse and return a number, not a Boolean. Thus using it in a condition is possible, but can result in weird logical outcomes.

Step 1: Make the query more debug friendly.
Capture.PNG
Then as I already wrote, functions used in queries must be prepared for two things: any kind of input data type, but especially NULL values and they must have foolproof error handling.

The basic layout is always like this:
Option Compare Database
Option Explicit

Public Function fGetNumber3(ByVal AValue As Variant) As Variant
  
  On Local Error GoTo LocalError

  fGetNumber3 = Null
  'Function body.
  Exit Function
  
LocalError:
  fGetNumber3 = Null
  Debug.Print "Error: " & Err.Number & " - " & Err.Description
  On Local Error Resume Next
  Debug.Print "For Value: [" & CStr(AValue) & "]"
  
End Function

Open in new window


Step 2:  Clean up the function. E.g.
Option Compare Database
Option Explicit

Public Function fGetNumber3(ByVal AValue As Variant) As Variant
' Return found Number or NULL
' Using Martin Liss's get last path part approach.

  On Local Error GoTo LocalError

  Dim Path() As String

  fGetNumber3 = Null
  If Len(Trim(AValue & "")) > 0 Then
    Path() = Split(CStr(AValue), "\")
    fGetNumber3 = Path(UBound(Path()) - 1)
  End If

  Exit Function
  
LocalError:
  fGetNumber3 = Null
  Debug.Print "Error: " & Err.Number & " - " & Err.Description
  On Local Error Resume Next
  Debug.Print "For Value: [" & CStr(AValue) & "]"
  
End Function

Open in new window


results now in
Capture.PNG
And now for the question: What do you expect from this function to return for each row? Cause your requirements (posts) are not really clear about that.
1
 
Bill NelsonITAuthor Commented:
I need to return all of the numbers at the end of the pathway....but no date as in the first record...I forgot that gets added in as a title holder...I need to strip it out of the query results, sorry....

So i need the numbers 1 to 23 inclusive...
0
 
ste5anSenior DeveloperCommented:
So the requirement is:
return the last part of the path, when it is a number?

Option Compare Database
Option Explicit

Public Function fGetNumber3(ByVal AValue As Variant) As Variant
' Return found Number or NULL
' Using Martin Liss's get last path approach.

  On Local Error GoTo LocalError

  Dim Path() As String

  fGetNumber3 = Null
  If Len(Trim(AValue & "")) > 0 Then
    Path() = Split(CStr(AValue), "\")
    fGetNumber3 = Path(UBound(Path()) - 1)
    If Not IsNumeric(fGetNumber3) Then
      fGetNumber3 = Null
    End If
  End If

  Exit Function
  
LocalError:
  fGetNumber3 = Null
  Debug.Print "Error: " & Err.Number & " - " & Err.Description
  On Local Error Resume Next
  Debug.Print "For Value: [" & CStr(AValue) & "]"
  
End Function

Open in new window

0
 
Bill NelsonITAuthor Commented:
yes exactly
0
 
ste5anSenior DeveloperCommented:
Okay. An now for the fun part: How do you define a number?
0
 
Bill NelsonITAuthor Commented:
any number from 1 to 99999
0
 
ste5anSenior DeveloperCommented:
Ready for fun with numbers?

any number from 1 to 99999
is not a precise definition.

Cause 9999e2 or 3.1412 are valid numbers according to your definition.

Do you mean
- (in math terms): natural number, rational numbers, irrational numbers, complex numbers, etc.
- (in comp terms): float, integers with what kind of format?

And what further constraints, like a range, needs to be applied to it?

A definition normally describes the data domain.
0
 
Bill NelsonITAuthor Commented:
I am meaning an Integer, always positive, no exponents...

Rarely will the folder count even approach the number 500.

(The program in it's entirety is reused and compacted after clearing out the tables...so it is reset and ready for the next day's import).
0
 
Bill NelsonITAuthor Commented:
I cannot thank you enough for sticking with me on this solution to my problem !!! You are a Saint and I just want you to know I sincerely appreciate you coming to the rescue !!!

Thanks also to Martin for trying your best to help a lost man in tge ACCESS VBA World
!!!! You are the very best guys!

Thanks,

Bill
0
 
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
 
Bill NelsonITAuthor Commented:
I will for certain....thanks so much Martin!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.