Parsing Function for Folder pathway

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

Select allOpen in new window

And then try this as...

GetNumber(<your string here>)

Select allOpen in new window

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

Select allOpen in new window

REgards

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

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.

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?

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)?

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?

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...

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 !

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)

And the Neeraj code needs to read:

GetNumber = Matches(0).Submatches(0)

Select allOpen in new window

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

Select allOpen in new window

Please read my prior comment

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

Select allOpen in new window

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

Select allOpen in new window

I am calling this module in the query grid as "fGetNumber3([FPath])"

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.

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?

For functions used in queries you should use a different signature:

Public Function GetNumber(ByVal AValue As Variant) As Long

Select allOpen in new window

And test the input before and do not rely on implicit conversion.

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..

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...

Post (attach) a sample database showing your problems..

Ok will do, just need to clear out some information first. Thanks so much for your help.

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.

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 !

Hi Martin,

Thanks for trying to help... I sincerely appreciate it !

Here is the database... I forgot to attach it.
EE-Folder-Number-v-1.zip

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.

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

Select allOpen 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

Select allOpen in new window

results now in

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.

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...

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

Select allOpen in new window

yes exactly

Okay. An now for the fun part: How do you define a number?

any number from 1 to 99999

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.

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).

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

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

I will for certain....thanks so much Martin!